Forum Discussion

thehalfboy's avatar
thehalfboy
Helper I
3 years ago
Solved

Filtering Rows adds a blank row at the bottom

I have a table in Power Query Editor, where the final step is filtering the rows down to the financial year, but when doing this, Power BI is adding a row of null values at the bottom of the table, which are then causing an error. They're not related to the raw data, it's an additional row that is being added on when the filter is applied. I can't for the life of me work out why it's doing it, so was hoping someone could shed some light on the matter!

 

 

#"Promoted Headers" = Table.PromoteHeaders(#"Savings Data_Sheet", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Procurement Contact", type text}, {"Procurement Team#(lf)Select from drop-down list", type text}, {"Atamis Original Workplan Ref", type text}, {"Contract Reference Number", type text}, {"Oracle Order Number", type text}, {"CSU Benefit#(lf)Select from drop-down list", type text}, {"Cost Centre#(lf)If multiple, enter ""See CC Tab"" and complete ""Cost Centre Data"" tab", type any}, {"Saving Start Date", type date}, {"Saving End Date", type date}, {"Contract Type#(lf)Select from drop-down list", type text}, {"Method of Saving#(lf)Select from drop-down list", type text}, {"Delivery Scale#(lf)Select from drop-down list", type text}, {"Project Details", type text}, {"Delivery Risk#(lf)Select from drop-down list", type text}, {"Full Year Effect inc VAT", type number}, {"Potential Saving in Year inc VAT", type number}, {"Months in Contract#(lf)(Current Financial Year)#(lf)Calculated Column", type number}, {"Potential Saving per Month inc VAT#(lf)Calculated Column", type number}, {"Comments", type text}, {"Column20", type any}, {"Month Start Number", Int64.Type}, {"Month End Number", Int64.Type}, {"Calculation", Int64.Type}, {"Date + 1", type date}, {"DateDif", Int64.Type}, {"Column26", Int64.Type}, {"Column27", type text}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Procurement Contact] <> null) and ([Saving Start Date] <> null)),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Saving Start Date", "Saving End Date", "Method of Saving#(lf)Select from drop-down list", "Delivery Risk#(lf)Select from drop-down list", "Potential Saving in Year inc VAT"}),
#"Added Custom" = Table.AddColumn(#"Removed Other Columns", "MonthSpan", each (12 * (Date.Year([Saving End Date]) - Date.Year([Saving Start Date])))
+ (Date.Month([Saving End Date]) - Date.Month([Saving Start Date]))
+ (if Date.Day([Saving End Date]) < Date.Day([Saving Start Date])
then -1
else 0
)
+ 1),
#"Added Conditional Column" = Table.AddColumn(#"Added Custom", "Month Span", each if [MonthSpan] >= 12 then 12 else [MonthSpan]),
#"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"MonthSpan"}),
#"Added Custom1" = Table.AddColumn(#"Removed Columns", "Savings per month", each [Potential Saving in Year inc VAT]/[Month Span]),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Month List", each List.Numbers(
1,
[Month Span]
)),
#"Expanded Month List" = Table.ExpandListColumn(#"Added Custom2", "Month List"),
#"Added Custom3" = Table.AddColumn(#"Expanded Month List", "Date", each Date.StartOfMonth(
Date.AddMonths(
[Saving End Date],
0 - [Month Span] + [Month List]
)
)),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom3",{{"Date", type date}}),
#"Removed Other Columns1" = Table.SelectColumns(#"Changed Type1",{"Saving Start Date", "Saving End Date", "Method of Saving#(lf)Select from drop-down list", "Delivery Risk#(lf)Select from drop-down list", "Savings per month", "Date"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Removed Other Columns1",{{"Savings per month", Currency.Type}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{"Method of Saving#(lf)Select from drop-down list", "Method of Saving"}, {"Delivery Risk#(lf)Select from drop-down list", "Delivery Risk"}}),
#"Inserted Month Name" = Table.AddColumn(#"Renamed Columns", "Month Name", each Date.MonthName([Date]), type text),
#"Added Conditional Column1" = Table.AddColumn(#"Inserted Month Name", "Target", each if [Month Name] = "April" then 333000 else if [Month Name] = "May" then 666000 else if [Month Name] = "June" then 999000 else if [Month Name] = "July" then 1330000 else if [Month Name] = "August" then 1600000 else if [Month Name] = "September" then 1900000 else if [Month Name] = "October" then 2200000 else if [Month Name] = "November" then 2500000 else if [Month Name] = "December" then 2950000 else if [Month Name] = "January" then 3250000 else if [Month Name] = "February" then 3500000 else if [Month Name] = "March" then 4000000 else null),
#"Changed Type3" = Table.TransformColumnTypes(#"Added Conditional Column1",{{"Target", Currency.Type}}),
#"Removed Columns1" = Table.RemoveColumns(#"Changed Type3",{"Month Name"}),
#"Filtered Rows1" = Table.SelectRows(#"Removed Columns1", each ([Date] = #date(2023, 4, 1) or [Date] = #date(2023, 5, 1) or [Date] = #date(2023, 6, 1) or [Date] = #date(2023, 7, 1) or [Date] = #date(2023, 8, 1) or [Date] = #date(2023, 9, 1) or [Date] = #date(2023, 10, 1) or [Date] = #date(2023, 11, 1) or [Date] = #date(2023, 12, 1) or [Date] = #date(2024, 1, 1) or [Date] = #date(2024, 2, 1) or [Date] = #date(2024, 3, 1)))
in
#"Filtered Rows1"

 

  • Is it possible that the filtering of the rows is bringing into the screen a bad row of data which already exists in the dataset?  The preview in Power Query loads 1000 rows as a default.  It may be that the data transforms all perform well on that 1000 rows of data but filtering the dataset exposes the error .

    You could try altering the Query editor to load all the data rather than 1000 rows so that you can investigate.  Just click on the bottom left of the main window in Power Query (where it says 'data profiling based on...' )to change the option to load all the data.

    --

    You could also try the data profiling features (after loading the entire dataset) 

    https://learn.microsoft.com/en-us/power-query/data-profiling-tools 

2 Replies

  • HotChilli's avatar
    HotChilli
    Community Champion

    Is it possible that the filtering of the rows is bringing into the screen a bad row of data which already exists in the dataset?  The preview in Power Query loads 1000 rows as a default.  It may be that the data transforms all perform well on that 1000 rows of data but filtering the dataset exposes the error .

    You could try altering the Query editor to load all the data rather than 1000 rows so that you can investigate.  Just click on the bottom left of the main window in Power Query (where it says 'data profiling based on...' )to change the option to load all the data.

    --

    You could also try the data profiling features (after loading the entire dataset) 

    https://learn.microsoft.com/en-us/power-query/data-profiling-tools 

  • Thank you, that was it, there was a divide by 0 happening somewhere earlier in the queries!