Please i need help formatting bad data.

[engrtee30]

Please I have issues with power bi refresh for my data source.

The data source is an excel file with several preformatted tables scattered all over the sheet. While preparing the dashboard, I used the keep, remove the top, and bottom rows functions to cut and size the sheet so I could extract the information I needed for the charts visuals.  For Example, Customer sales data is on the cell range A1:D20 and order data is from cells A22:D40, and so on.

Now when I am trying to refresh with updated data(the dashboard is meant to be updated weekly), the rows are increasing and we are having something like Customer sales data is now A1:D35 and order data is now A37:D57.  

The challenge now is my power query steps has already been configured to pick the first set of rows (i.e A1:D20) and this new extension of data would not be featured because it exceeds it. 

What can i do to make PowerBI capture the data even if the rows are exceeded or is there any other way around this?

Please i really need help and i would be looking forward to our responses

[engrtee30 · Accepted solution]

I have been able to resolve it. 

All I needed to do was to create a template sheet in excel and create a dynamic table as seen in the attached picture. Anytime new rows are added, the table would expand automatically and power bi would be able to capture its values.

[engrtee30]

This is an example of the original data i used to prepare the dashboard. I used the keep rows function in power query to cut out the table i neededThis is an example of how the updated one would be with increased or a decreased number of rows

[Mahesh0016]

@engrtee30 Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.