Forum Discussion

workingdataprep's avatar
workingdataprep
New Member
10 months ago
Solved

Handling Excel Matrixed Data

Hi all! How do I perform ETL on this data so that it is optimized for Power BI in a "tall" format with four columns: Type, Category, Date Range, and Sales Amount. I've tried a couple different ways b...
Need Help
Show and Tell
  • raisurrahman's avatar
    raisurrahman
    10 months ago



    Is it your required format? workingdataprep 

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type any}, {"Column4", type any}, {"Column5", type any}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}}),
    #"Transposed Table" = Table.Transpose(#"Changed Type"),
    #"Filled Down" = Table.FillDown(#"Transposed Table",{"Column1"}),
    #"Merged Columns" = Table.CombineColumns(#"Filled Down",{"Column1", "Column2"},Combiner.CombineTextByDelimiter("||", QuoteStyle.None),"Merged"),
    #"Transposed Table1" = Table.Transpose(#"Merged Columns"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Type||", type text}, {"Category||", type text}, {"Jan 1 - Jan 30||Actual", Int64.Type}, {"Jan 1 - Jan 30||Planned", Int64.Type}, {"Feb 1 - Feb 28||Actual", Int64.Type}, {"Feb 1 - Feb 28||Planned", Int64.Type}, {"Mar 1 - Mar 30||Actual", Int64.Type}, {"Mar 1 - Mar 30||Planned", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Type||", "Category||"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter("||", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{"Type||", "Type"}, {"Category||", "Category"}, {"Attribute.1", "Date Range"}, {"Attribute.2", "Actual vs Planned"}, {"Value", "Sales Amount"}})
in
    #"Renamed Columns"


    I’m assuming the dataset is quite simple. However, if you’re working with a larger dataset, you’ll need to handle the nested headers separately.

KarinSzilagyi's avatar
KarinSzilagyi
Icon for Super User rankSuper User
10 months ago

Hi workingdataprep, since you only want 4 columns and the last column is called "SalesAmount", I assume you only want to keep the Actuals and don't need the Planned values for your use-case.

Step 1: Make sure that your data looks exactly as your example in PowerQuery Editor (=> remove any "Promoted Headers" step if your columns are NOT called Column1, Column2 etc.)

Step 2: Select Column1 + Column2 and use Transform > Transpose

 

Step 3: Select only Column1 and use Transform > Fill > Down

 

Step 4: Since you only mentioned 4 Columns and the last column was "SalesAmount", you can now filter out "Planned" (keep nulls and "Actual") from Column2

 

Step 5: Select only Column1 + Column2 again and use Transform > Transpose again

 

Step 6: Use Transform > "Use First Row as Headers" to promote Type, Category and the DateRanges to the column headers

 

Step 7: Filter out the "Actual" row by either filtering out the nulls or the "Actual" text from any of the columns

 

Step 8: Select the columns Type + Category and use Transform > Unpivot Columns > Unpivot others 


Step 9: Change Column Names and Datatypes as needed