He has system generated reports that look like the following:
Which when you bring into Power BI, still look like that.
How then do you turn them into this:
... but in Power BI.
With help from Matt Allington's amazing blog, I got what will do the magic.
We create Custom queries to grab and store the region and company before promoting the actual header row to being the header.
Click on the fx right beside the formula edit space to add a query step.
We then add two custom queries, one to grab the region and another to grab the company name.
The first is:
= Record.Field(Source{[Item="Sheet1",Kind="Sheet"]}[Data]{0},"Column8")
Which is essentially telling Power Query to grab the first cell entry on the Column named Column8. For the part inside the Record.Field but before the {0}, just copy paste the last query in there.
We do another custom query to grab the company name.
Now that we have grabbed and stored those cell entries, we can now go back to the source table and do our normal header row promotion.
To get back to the source table, just do another add step but paste in it the exact query in the step before our first custom query.
What is left is to create Custom Columns with those saved values.
And that was it! Just move them to the beginning of the table.
Okay, that's not entirely true. That wasn't just all we did. He had many of those system generated files and already autosave them in a folder. So we needed to import the folder and have Power Query combine the files but first do these transformations before combining the files.
However, this good enough for today's post. Here's the Power BI practice file: https://urbizedge.blob.core.windows.net/urbizedge/Sample%20Data%20for%20Power%20Query%20custom%20cell%20entries%20grabbing.pbix
Enjoy!
0 comments:
Post a Comment
You can be sure of a response, a very relevant one too!
Click on Subscribe by Email just down below the comment box so you'll be notified of my response.
Thanks!