Power Query: Promoting column headers
Post Tags: Power Query | See all tags
This post was made with the February 2024 version of Power BI Desktop.
We’re exploring a really common function that comes with Power Query editor today, and that’s how to promote column headers in your data if Power BI can’t figure out what your headers are.
This post (and video below) uses a new blank Power BI report and connects some simple data to introduce how to fix this issue. To download the source Excel data file this report uses to try out what I do in this post and video, here is the data file you can connect to Power BI Desktop:
Here’s the video. Written version is below the video.
Sometimes, when we connect to data, it doesn’t look quite right. One of the most common “there’s something wrong here” issues is when Power BI has trouble figuring out column headers.
When it has trouble with this, it just basically says “whatever!” and puts column headers in the actual data rows, which means we have to fix it.
Let’s connect to our data and I’ll show you what I mean and the really easy fix for it.
Click that green “Import data from Excel” button (we also have a post just about connecting to Excel data) and navigate to the “Promoting Headers” Excel file linked above if you downloaded it (or your own data if you have Power BI connecting to data and it can’t figure out the column headers). Click open to connect to the data.
Then we’ll click our Data tab that’s in our spreadsheet, and we’ll get a preview of our data.
We have a Region column, a Program column, and a 2020 column with values. Power BI can’t figure out what the column headers are here, so it put our column headers in the first row of data, and just named our columns Column1, Column2, and Column3.
If you have Power BI doing this to you (and it happens to us all), don’t worry. It’s easy to fix.
Let’s make sure our Data tab is checked and we’re going to hit the Transform Data button at the bottom to open up Power Query Editor. (You can check out the Intro to Power Query post to find other ways to access Power Query Editor)
There’s our data in Power Query Editor, with our actual column headers in the first row of data.
This happens frequently enough that Microsoft made a whole damn button in the ribbon to address it.
We’re going to click the button that is called “Use First Row as Headers”.
That’s it! Our proper column headers are now our column headers! Done and done, and easy to do!
You can now click “Close and Apply” at the very left side of the Power Query Editor Home ribbon, and that change will be applied to how Power BI is reading your data source and you’ll be ready to rock!
If you have a question about Promoting headers, leave a comment below and we’ll figure it.
Take care,
Joe.
Post Tags: Power Query | See all tags