Power Query: Heading Off – Part 8
3 April 2024
Welcome to our Power Query blog. Today, I look at using M code to provide an alternative solution to an issue that occurs when I load to a Table with no headers.
I plan to show a particular issue with Power Query and Tables without headers. However, first I need to create the scenario, and I will show a few methods and tips along the way. I have two Tables of data:
- contains my salespeople’s expenses (Expenses)
- determines the expenses that will be covered by each supplier (Supplier_Limit).
In Part 1, I created two [2] queries, and grouped Expenses.
In Part 2, I merged Expenses with the Supplier_Limit query to create Limit_Exceeded, which tells me if any limits have been breached.
In Part 3,I loaded Limit_Exceeded to a new worksheet.
I removed the header row and changed some of the data in Expenses.
In Part 4, I ran the query for a single salesperson.
I extracted this cell and created a parameter P_Salesperson:
I used this parameter to limit the data in Expenses:
When I refreshed Limit_Exceeded, I checked the results:
The results correctly showed one row of data and no headings. However, things changed in Part 5 when I selected a different Salesperson:
When I refreshed Limit_Exceeded, it had moved the data from the previous selection into the header row.
When I deleted the top row from the Excel sheet, things got worse!
In Part 6, I changed what happens if there is no data returned by Limit_Exceeded. I created a basic query with one [1] blank row and appended it to Limit_Exceeded.
I deleted Column1, and loaded the results to the Excel workbook:
This is looking much better! Clearly, it’s not ideal to have a blank row, but it is better than showing the wrong data. Last time, I refined this approach so that I don’t add a blank row if I have data.
This time, I will look at an alternative way to achieve a similar result using M code, rather than appending query NoRows. I make a copy of Limited_Exceeded. I take a duplicate copy, as I will be changing the steps:
I call the new query Limit_Exceeded_NoAppend. I open the ‘Advanced Editor’ from the Home tab:
Instead of appending table NoRows, I will use M code to add a row. The function I will use is:
Table.InsertRows(table as table, offset as number, rows as list) as table
This function returns the table, table with the list of rows, rows, inserted into the table at the given position, offset.
This will remove the need to remove a column after I have inserted a row, since I can match the existing columns. I will change the following section of M code:
Limits_Exceeded = Table.RemoveColumns(#"Filtered Rows",{"Flag"}),
Check_NoRows = Table.IsEmpty(Limits_Exceeded),
#"Appended Query" = if Check_NoRows then Table.Combine({Limits_Exceeded, NoRows}) else Limits_Exceeded,
#"Removed Columns1" = if Check_NoRows then Table.RemoveColumns(#"Appended Query",{"Column1"}) else Limits_Exceeded
in
#"Removed Columns1"
I replace it with this:
Limits_Exceeded = Table.RemoveColumns(#"Filtered Rows",{"Flag"}),
Check_NoRows = Table.IsEmpty(Limits_Exceeded),
AddRow = if Check_NoRows then Table.InsertRows(Limits_Exceeded,0,{[Supplier=null, #"Expense Type"=null,Amount=null,#"Expense Limit"=null]}) else Limits_Exceeded
in
AddRow
This adds a single row with null values in all columns:
If I load this query to a table with no headers, I get the desired result:
I may add an indicator message if output is required to make it clear that no records were returned:
I have put “No Records Selected” in the Supplier value in the inserted row. When I load this, the results are perhaps clearer:
Note that I could also have done this using the method where I appended query NoRows. I would have needed to use this phrase instead of the empty speech marks for the source:
In either case, this will only make sense if I have the check for zero rows before the line is added.
Come back next time
for more ways to use Power Query!
