Forum Discussion

Lio123's avatar
Lio123
Advocate I
1 year ago
Solved

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...
Need Help
  • danextian's avatar
    danextian
    1 year ago

    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's avatar
Lio123
Advocate I
1 year ago

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's avatar
    jaineshp
    Memorable Member
    1 year ago

    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