Forum Discussion
Data to not include blank rows
Excel spreadsheet is the data source. To make it easy for the users to enter data, I created an area with borders around the cells. This is the area for users to enter data. I than created a Table Name around the bordered cells. Note The area contains blank rows/cells.
In PowerBI, I connect to the Excel spreadsheet and select the Tables, and not the spreadsheet tabs. The tables are great, as column headers/field names are available.
The problem is in PowerBI, Table View, the table has blank rows.
My totals counters include the blank rows therefore the totals are incorrect.
Total rows 29 rows
Total rows contain actual data = 6
Blank rows = 23
What is the best way to not have blank rows? and make the total counters correct
Will there be any issues
In the past I was in Transform Data, Reduce Rows, Remove Bottom rows, set the range ie row 10 - 20.
The result was when new data was entered at row 11, the row was not displayed in the powerbi report.
TIA
Hi dd88
I would recommend using the "Remove Blank Rows" function in the Power Query interface.
Home > Remove Rows > Remove Blank Rows
This is translated into M code looking like this
= Table.SelectRows(YourTable, each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null})))which keeps only rows containing at least one value that is neither an empty string nor null.
Does this work for you?
Regards
2 Replies
Hi dd88
I would recommend using the "Remove Blank Rows" function in the Power Query interface.
Home > Remove Rows > Remove Blank Rows
This is translated into M code looking like this
= Table.SelectRows(YourTable, each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null})))which keeps only rows containing at least one value that is neither an empty string nor null.
Does this work for you?
Regards
