Picking Specific Excel Cell Entry In Power BI and Turning it into A Column

Last week, I got a call from a past participant of our Power BI training regarding an issue he was battling. We provide after training support for all our training, which is one huge value most other training providers don't provide, especially those who don't have in-house dedicated trainers with deep consulting expertise.

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!