Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
engrtee30
New Member

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

1 ACCEPTED SOLUTION
engrtee30
New Member

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_0-1673283159315.png

 

View solution in original post

3 REPLIES 3
engrtee30
New Member

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_0-1673283159315.png

 

engrtee30
New Member

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 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 rowsThis is an example of how the updated one would be with increased or a decreased number of rows

Mahesh0016
Super User
Super User

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors