Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
mwvoves
Regular Visitor

Promoting headers that don't match

I've got a series of date excel files where there are a few misc rows of data at the top, then has the dates listed horizonally, one per column (see picture below). I'd like to write a query to combine and transform these files in Power BI, which are all in a shared folder, after they are unpivoted. I can go into the "Transform Sample File" and get it to work using a series of steps, but the trasnformations only work for the first file. Looking at the error on the subsequent files it appears the error comes when I promote the "Reg Hours" row below as headers the second and third files error out due to not having the same header names (because they are for 1-feb and not 1-Jan for example). Is there any way to fix this?

 

mwvoves_0-1686074222504.png

 

1 ACCEPTED SOLUTION
TheoC
Community Champion
Community Champion

Hi @mwvoves 

 

I've attached a PBIX file for you.

 

Firstly, you want to Transpose the table (in the Transform tab of Power Query), then Unpivot.

 

You will have to delete your second Applied Step though, as Power BI is automatically determining your first header.

 

Let me know whether the attached is what you are after. The outputs turn out like this:

TheoC_0-1686084940004.png

 

Hope this helps!

Theo 🙂

 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

View solution in original post

4 REPLIES 4
TheoC
Community Champion
Community Champion

Hi @mwvoves 

 

Rather than promoting the Dates as "Headers,"  you should unpivot the columns so that your result is a "Dates" column and an "Amount" column.  This will then give you the basis of creating a relationship to a Date table and allow you to also undertake far better analysis comparatively.

 

Let me know if you'd like further explanation k!

 

Theo 🙂

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

Here's how the table loads into power BI- are you saying I should unpivot the table as is (instead of removing the top 2 rows/promoting the first row as headers)?

 

mwvoves_0-1686082384059.png

 

TheoC
Community Champion
Community Champion

Hi @mwvoves 

 

I've attached a PBIX file for you.

 

Firstly, you want to Transpose the table (in the Transform tab of Power Query), then Unpivot.

 

You will have to delete your second Applied Step though, as Power BI is automatically determining your first header.

 

Let me know whether the attached is what you are after. The outputs turn out like this:

TheoC_0-1686084940004.png

 

Hope this helps!

Theo 🙂

 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

Yes I was able to get it to work after using the transpose step, thank you!!

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors