Forum Discussion
Please i need help formatting bad data.
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
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.
3 Replies
engrtee30 Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
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
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.
