Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
How can I (can I?) implement a row check step, just after importing a table, that will perform row count of imported dataset and compare it to the dataset's expected rowcount?
I would like to create a post import step that will determine if actual rowcount is as expected.
Background:
For each table in my power bi desktop model I have an URL that was provided to me by a developer, which executes a json format query he has written. I use the URL with Get Data->From Web and it retrieves all the data i need for a given table.
But, since sometimes the imported dataset has too few rows I have asked the developer to add a rowcount column so that i may always know if the imported dataset is what the developer intended. I hope there is a way to compare my rowcount with developers, for quality control.
Solved! Go to Solution.
@hxkresl wrote:
So, are you saying the data load will be all successf or all failure with no chance of partial load?
The Developer asked if ld be fine with separate table providing the row count, as there is some difficulty providing within original query.
If I am getting seperate queries returning rowcount for tables, any best practices for automating the rowcount check?
Yes.
Not a best practices but you can do that with Power Query. Just replace the Source with your rowcount table. You'll have to count all rows for all tables and then the rowcount table would change according to subsequential data refresh. Check a demo in the pbix attached.
let Source = Table.FromRows({{"Table1",1},{"Table2",2},{"Table3",3}},{"tableName","expectedRowcnt"}), #"Changed Type" = Table.TransformColumnTypes(Source,{{"tableName", type text}, {"expectedRowcnt", Int64.Type}}), GetTable1RowCnt = Table.FromRows({{"Table1",Table.RowCount(Table1)}},{"tableName","actualRowcnt"}), GetTable2RowCnt = Table.FromRows({{"Table2",Table.RowCount(Table2)}},{"tableName","actualRowcnt"}), GetTable3RowCnt = Table.FromRows({{"Table3",Table.RowCount(Table3)}},{"tableName","actualRowcnt"}), UninonRowCntTbls = Table.Combine({GetTable1RowCnt,GetTable2RowCnt,GetTable3RowCnt}), MergeSourceAndUninonRowCntTbls = Table.Join(Source ,"tableName", UninonRowCntTbls ,"tableName") in MergeSourceAndUninonRowCntTbls
@hxkresl wrote:
How can I (can I?) implement a row check step, just after importing a table, that will perform row count of imported dataset and compare it to the dataset's expected rowcount?
I would like to create a post import step that will determine if actual rowcount is as expected.
Background:
For each table in my power bi desktop model I have an URL that was provided to me by a developer, which executes a json format query he has written. I use the URL with Get Data->From Web and it retrieves all the data i need for a given table.
But, since sometimes the imported dataset has too few rows I have asked the developer to add a rowcount column so that i may always know if the imported dataset is what the developer intended. I hope there is a way to compare my rowcount with developers, for quality control.
Where is the rowcount column? In the JSON body as well? Then I think you can extract that rowcount and compare it to the rows expanded.
By the way, usually Power BI won't miss data from API/URL as long as the JSON are correctly extracted, so I don't have concern that data is not imported.
So, are you saying the data load will be all successf or all failure with no chance of partial load?
The Developer asked if ld be fine with separate table providing the row count, as there is some difficulty providing within original query.
If I am getting seperate queries returning rowcount for tables, any best practices for automating the rowcount check?
@hxkresl wrote:
So, are you saying the data load will be all successf or all failure with no chance of partial load?
The Developer asked if ld be fine with separate table providing the row count, as there is some difficulty providing within original query.
If I am getting seperate queries returning rowcount for tables, any best practices for automating the rowcount check?
Yes.
Not a best practices but you can do that with Power Query. Just replace the Source with your rowcount table. You'll have to count all rows for all tables and then the rowcount table would change according to subsequential data refresh. Check a demo in the pbix attached.
let Source = Table.FromRows({{"Table1",1},{"Table2",2},{"Table3",3}},{"tableName","expectedRowcnt"}), #"Changed Type" = Table.TransformColumnTypes(Source,{{"tableName", type text}, {"expectedRowcnt", Int64.Type}}), GetTable1RowCnt = Table.FromRows({{"Table1",Table.RowCount(Table1)}},{"tableName","actualRowcnt"}), GetTable2RowCnt = Table.FromRows({{"Table2",Table.RowCount(Table2)}},{"tableName","actualRowcnt"}), GetTable3RowCnt = Table.FromRows({{"Table3",Table.RowCount(Table3)}},{"tableName","actualRowcnt"}), UninonRowCntTbls = Table.Combine({GetTable1RowCnt,GetTable2RowCnt,GetTable3RowCnt}), MergeSourceAndUninonRowCntTbls = Table.Join(Source ,"tableName", UninonRowCntTbls ,"tableName") in MergeSourceAndUninonRowCntTbls
Sorry i have not been able to implement it in my own environment yet, but I opened the zip and followed the logic. It should work for my needs. Will update as soon as possible.
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
6 | |
6 | |
3 | |
2 | |
2 |
User | Count |
---|---|
6 | |
5 | |
4 | |
4 | |
3 |