SOLVED

Standardizing a report

Brass Contributor
Hello!  I have a bit of a problem... I have a file with all of my data in it, but it's not friendly for PowerBI input... I was wondering if there is a way to create a macro/formula to change the format?

 

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:

nattiej101_0-1706883546245.png

 

 

15 Replies

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:

Sample.png

 

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?

@nattiej101 

 

Back to my point Don't record as you currently do for next years

 

If you record in a Table like below:

Sample.png

leaving cells empty (as the greyed out ones above) when this is N/A:

  1. You won't have anymore problems uploading to Power BI
  2. 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:

PowerPivot.png

 

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
So sorry! The N/As can be null values. Or, places as N/As and I can manually move those over to nulls

@nattiej101 

 

OK, "N/As" are replaced with null values in attached query

 

In the PARAM sheet you'll have to update the path to your OR Report workbook then swith to sheet 'CombinedYears' > Right-click in the table > Refresh

You're welcome...

@L z. 

So sorry! I didn't see the reply (looks like I didn't get the email)

I still can't update the query... this is the error message I receive:

 

nattiej101_0-1708090344340.png

 

@nattiej101 

 

In your OR_Report you not only have N/A but 11 occurences of NA. This problem explains the error you get. I updated the query to deal with this: any value in [Planned OR%] and [Line Result] that can't be converted to a number is replace with null

Now I'm getting this error:

 

I'm just confused and curious as to how to fix these issues on my own as well.

nattiej101_0-1708112966042.png

 

@L z. 

@nattiej101 

 

sample.png

 

For whatever reason the file appears to be locked by someone/another app.

Worst case, save & close everything > Restart your PC

@L z. It's still not working.... can you please explain to me how you created this Query so I can try to create it and manage it as well?

 

I'm still getting the Null to Text error too:

nattiej101_0-1708351291961.png

 

best response confirmed by nattiej101 (Brass Contributor)
Solution

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:

sample.png

 

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?

I was able to fix it! I had to scrub my data a bit more - it had a hidden row. Thank you so much for all of your help.
Glad you found the root cause yourself & Thanks for feedback
1 best response

Accepted Solutions
best response confirmed by nattiej101 (Brass Contributor)
Solution

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:

sample.png

 

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?

View solution in original post