Forum Discussion
Standardizing a report
I had a request earlier, but it wasn't able to refresh.
Any advice/tips on how to combine this data is MUCH appreciated! I would really love to not have to manually manipulate each by hand.
I've attached the report - but not sure how to create it into a format like this:
Hi nattiej101
can you please explain to me how you created this Query so I can try to create it and manage it as well?
Don't get me wrong please but if you don't already have a good knowledge of Power Query this sounds unrealistic. What you can/should do:
In Excel:
- Go to Data (tab) > Queries & Connections (the corresponding pane opens on the right)
- Right-click on query CombinedYears (Power Query Editor will open
In Power Query Editor:
- In the APPLIED STEPS (on the right) click each step, one after the other to see what happens (in the middle of the screen)
PQ documentation is avail. here
I'm still getting the Null to Text error too
I built the query on the file you initially shared (Feb 02 2024 03:20 PM). Following my recent update the query Refreshes no problem here, with 0 error:
This means the file you currently use - as Source - has something different than the one you shared
Could you post the file with which you get Error We cannot convert the value null to type Text?
Hi nattiej101
I have a file with all of my data in it, but it's not friendly for PowerBI... I would really love to not have to manually manipulate each by hand
I would be tempted to say don't record as you currently do for next years otherwise (except if you dev. Power Query skills) you'll keep relying on forums
Back to I have a bit of a problem...not sure how to create it into a format like this:
As you can see above this is done here. However, I filtered the result to show you something that's going to cause a problem if you upload this to Power BI:
In [Planned OR%] & [Line Result] you have N/As that cannot be converted to Numbers of course
Consequently those columns cannot be Typed (Decimal Number) and any unTyped column is typed Text in Excel Data Model & Power BI, making any aggregation (SUM, AVG...) impossible
==> Decision?
Back to my point Don't record as you currently do for next years
If you record in a Table like below:
leaving cells empty (as the greyed out ones above) when this is N/A:
- You won't have anymore problems uploading to Power BI
- You can auto. get (in Excel with a Power PivotTable) something very close to what your currently do manually - order of the columns can be changed of course:
Sample attached. Hope this makes sense & helps
- I will let them know going forward. But is there a way to create the data that I have in that format?
- Read my 1st reply. The answer is YES but there is a question for you re. the N/A
