How to check that all expected rows were imported to power bi desktop model?

[hxkresl]

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.

[Eric_Zhang]

---

@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. 

---

@hxkresl

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.

[hxkresl]

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?

[Eric_Zhang · Accepted 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]

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.