Forum Discussion
Urgent Help - Power Query - Data Transformation
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.
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.
23 Replies
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.Hey Lio123,
I understood you need to keep months and customer names separate - here's the approach for your scenario:
Quick Solution:
- Fill Down the first row (months) to cover all cells below
- Skip the original month header row
- Promote Headers - this gives you customer names as column headers
- 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
RenameHeadersFor Power BI Matrix Visualization:
Once you have the data transformed:
- Rows: Use your first 3 static columns (ID, Product, Category)
- Columns: Add a custom column in Power Query to identify the month for each customer
- Values: The customer data values
Fixed? ✓ Mark it • Share it • Help others!
Best Regards,
Jainesh Poojara | Power BI Developer
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.
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.
Hey Lio123 ,
Looking at your double header structure, here's how to handle this in Power Query:
Step-by-Step Solution:
- Promote Headers: Use "Promote Headers" twice to get both header rows as column names
- Merge Header Names: Create custom column to combine the double headers:
= [Column1] & " - " & [Column2] - Keep First 3 Columns: Leave your initial columns (likely ID, Name, etc.) unchanged
- Unpivot Remaining Columns: Select columns 4 onwards → Transform → Unpivot Columns
- Split Attribute Column: Split the merged header names back into separate columns using " - " as delimiter
- Pivot Back: Pivot on one of the split header values to recreate your matrix structure
Alternative Approach:
- Skip to header row 2, promote headers
- Manually rename columns to include both header information
- Then proceed with normal unpivot/pivot operations
Pro Tip: If your source data changes frequently, use dynamic column selection with Table.ColumnNames() function instead of hardcoding column positions.
This should give you a clean matrix that Power BI can work with effectively. Test with a small sample first to ensure the transformation logic works correctly.
Best Regards,
Jainesh Poojara | Power BI DeveloperHi 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.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,
DineshHi 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
Hi Lio123,
Is this what you are loooking for? I'll leave the images and file link below; ThanksHi 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.
Hello danextian,
The response in which you are referring has been removed from this post.
Best,
Natalie H.
Community Manager
