Urgent Help - Power Query - Data Transformation

[Lio123]

Dear Developers, 

My data looks like below, I want build the same matrix in Power BI. Can you please help me how to handle this in Power Query data transformation. This is a double header case but first 3 colums would remain same for other columns.

[danextian · Accepted solution]

Hi @jaineshp 

You will need to shape your data so the two headers are parallel in two separate columns. You can do that by accessing  the first and second row before promoting the  headers and use the combination of the two as the new column names. Unpivot your data after then split the attribute into two separate columns.

let
    Source = Excel.Workbook(File.Contents("D:\testdata.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Automatically Renamed Columns" = 
        let 
            tbl = Sheet1_Sheet,
            header1 = Record.ToList(Sheet1_Sheet{0}),
            header2 = Record.ToList(Sheet1_Sheet{1}),
            zipped = List.Zip({header1, header2}),
            combined = List.Transform(
                zipped,
                //each (if _{0} = null then _{1} else _{0}) & "__" & _{1}
                //each (if _{0} = null then _{1} & "" else _{0}) & "__" & _{1}
                each (if _{0} = null then "" else _{0}) & "__" & _{1}
            ),  

            OriginalColumns = Table.ColumnNames(tbl),
            renamevalues = List.Zip({OriginalColumns,combined }),
            renamed = Table.RenameColumns(tbl, renamevalues)
        in
            renamed,
    #"Removed Top Rows" = Table.Skip(#"Automatically Renamed Columns",2),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Top Rows", {"__Type", "__Qty", "__USD"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"__Type", "Type"}, {"__Qty", "Qty"}, {"__USD", "USD"}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Renamed Columns", "Attribute", Splitter.SplitTextByDelimiter("__", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Split Column by Delimiter",{{"Attribute.1", "Month"}, {"Attribute.2", "Category"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns1", "Month Sort", each Date.Month(Date.From([Month] & "1, 2025"))),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Value", Int64.Type}, {"Month Sort", Int64.Type}, {"Type", type text}, {"Qty", type text}, {"USD", type text}})
in
    #"Changed Type"

Please see the attached sample pbix.

[Lio123]

Hi @danextian , Fantastic, thank you so much!

Can you also please provide the explanation along with the excel which has been used in this pbix?

Regards!

[danextian]

This is the excel file I used (moving forward, please post a workable sample data, not an image, not everyone would want to manually type the data). If you pasted the code to AI and asked for an explanation, it would give you quite a decent explanation - probably a lot bettter than i would.

[danextian]

Another untested AI-generated response. How is this related to @Lio123 's question? There isn't a mention of quarter or sales or target in his post. 

[Natalie_iTalent]

Hello @danextian,

The response in which you are referring has been removed from this post.

Best,

Natalie H.

Community Manager 

[Lio123]

Hi @jaineshp 

I do not want to combine the headers, both are completely different things and I want them separately. First header is for months and second header is for customer names. 

And first  3 columns would remain same for other columns as I will have to muliple these with other columns based on some basiness condition. 

[jaineshp]

Hey @Lio123,

I understood you need to keep months and customer names separate - here's the approach for your scenario:

Quick Solution:

1. Fill Down the first row (months) to cover all cells below
2. Skip the original month header row
3. Promote Headers - this gives you customer names as column headers
4. Your first 3 columns stay intact, other columns now have customer names

Power Query Steps:

= Table.FillDown(Source, Table.ColumnNames(Source))
= Table.Skip(FillDown, 1)
= Table.PromoteHeaders(SkipFirstHeader)

Result: You'll have customer names as headers, and the month info becomes part of your data (first row). Perfect for applying business logic later.

Alternate Approach:

let
Source = Excel.Workbook(File.Contents("YourFile.xlsx"))[Data],

// Step 1: Extract the month header row
MonthHeaders = Table.ToList(Table.FirstN(Source, 1)){0},

// Step 2: Extract the customer header row
CustomerHeaders = Table.ToList(Table.Skip(Table.FirstN(Source, 2), 1)){0},

// Step 3: Create new column names combining both
NewHeaders = List.Transform(
List.Positions(CustomerHeaders),
each if _ < 3
then CustomerHeaders{_} // Keep first 3 columns as-is
else CustomerHeaders{_} & "_" & MonthHeaders{_}
),

// Step 4: Skip both header rows and apply new headers
DataOnly = Table.Skip(Source, 2),
RenameHeaders = Table.RenameColumns(
DataOnly,
List.Zip({Table.ColumnNames(DataOnly), NewHeaders})
)
in
RenameHeaders

For Power BI Matrix Visualization:

Once you have the data transformed:

1. Rows: Use your first 3 static columns (ID, Product, Category)
2. Columns: Add a custom column in Power Query to identify the month for each customer
3. Values: The customer data values
   

Fixed? ✓ Mark it • Share it • Help others!

Best Regards,
Jainesh Poojara | Power BI Developer

[SundarRaj]

Hi @Lio123,
Is this what you are loooking for? I'll leave the images and file link below; Thanks

https://docs.google.com/spreadsheets/d/1J9BJxgXCdfYlgHHBE3Peqw4Y9hp5z8WT/edit?usp=sharing&ouid=10631...

[Lio123]

Hi @SundarRaj 
Thank you for your response.

Yes, you are right, this is the data I will have to deal with. Also, please note, values of double header S, T, X, Y these would change every time, basically this the customer name. It changes every month.
I'm unable to open the link, would you mind providing the sample PBI file directly.

[SundarRaj]

Hi @Lio123 ,
Yes, that makes sense. The customer names would change dynamically here since no change has been done to them. Just that the double headers issue has been solved.

Thanks,
Sundar

[Lio123]

Hi @SundarRaj ,

The data in the excel is perfect, but how to handle this in Power Query/ Power BI ?

[Sundar2600]

Hey @Lio123,

Open the Excel file and just launch the Power Query Editor through the search bar. You'll see all the steps associated with the query and the output. Let me know in case of any issues. If you'd like detailed steps explanation, do let me know as well. Thanks

[Lio123]

Hi @SundarRaj / @v-dineshya 

Thanks for your response, however, in the excel PQ which have you shared, still has double header.
My current data also looks excalty the same as of your output, can you please suggest how to handle from here.

Please note : first 3 columns would remain same for all other columns.

Below screenshot is from your excel PQ

Regards!

[v-dineshya]

Hi @Sundar2600 , Thank you for your prompt response.

Hi @Lio123 , Could you please try the proposed solution shared by @Sundar2600 , Please do let us know if you have any further queries.

Regards,

Dinesh

[Omid_Motamedise]

See following video of mine in this regards

https://www.youtube.com/watch?v=S2PpaWZeRCg

[Lio123]

Hi @jaineshp ,

Appriciate your efforts making me understand. 

First header is month (Jan to Dec) 
Second header is customer name ( this will change month on month)

First 3 columns would be same for all columns ( row wise)

Also, end result should look something similar to the screenshot I posted previously.
And my data also looks the same way as I wanted the output to be ( same as screenshot)

Please provide me the sample pbix file as I'm new to Power BI, it is quite difficult to follow the steps.

[v-dineshya]

Hi @Lio123 ,

Thank you for reaching out to the Microsoft Community Forum.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot). Do not include sensitive information. Do not include anything that is unrelated to the issue or question. Please show the expected outcome based on the sample data you provided.

Regards,
Dinesh

[v-dineshya]

Hi @Lio123 ,

We haven’t heard from you on the last response and was just checking back to see,  Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot). Do not include sensitive information. Do not include anything that is unrelated to the issue or question. Please show the expected outcome based on the sample data you provided.

Regards,
Dinesh

[Lio123]

Hi @v-dineshya ,

Thank you so much for contacting,

There is an excel in the same post, uploaded by @SundarRaj . please refer that excel.
Additionally I'm attaching the data screenshot as I won't be able to upload the file from my organization system. Please help me around with this issue, I have been looking for the solution. 

This is the data I will have to deal with. Also, please note, values of 2nd double header S, T, X, Y these would change every time, basically this the customer name. It changes every month.

First 3 colums would remain same for other columns


My excel looks like this and I want to show the same thing in Power BI matrix.
Basically input from excel and output from Power BI matrix look the same. 

It would really helpful if you could share the sample PBI @v-dineshya 

Thanks!

[Lio123]

Dear @v-dineshya,

Can you please help to resolve handle this use case?

Thanks!