Handling Excel Matrixed Data

[workingdataprep]

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 but the second row seems to mess up all ETL I try to do. Any way to get this in a clean format?

[raisurrahman · Accepted solution]

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.

[workingdataprep]

Yes this is the required format thank you so much! Is this best practice for nested headers usually? Are there resources to help better understand how to deal with these?

[raisurrahman]

@workingdataprep

For small dataset this is great. As I said yesterday, if you have large dataset then reference the query into two parts-header and body. Then process it. I can suggest two resources for you:

1.  Master Your Data with Excel and Power BI by Ken Puls and Miguel Escobar

2.  Power Query Recipes by Ken Puls (https://skillwave.training/shop/power-query-recipes/)

If you need any assistance, please feel free to contact me
Regards

Rais
 

[KarinSzilagyi]

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

[SundarRaj]

Hey @workingdataprep ,
On the basis of what you have described, I have managed to do some transformations. Let me know if this is what you are looking for. If this isn't, do put the sample output of your file in order to get a better idea. I'll attach the image of the output and reference file of the output.

PS: The below file has the required new changes made.

Thanks,

[KarinSzilagyi]

Hi @SundarRaj, just as a note: Your solution mixes the Planned and Actual Sales Values into one column without any way to distinguish them, which I personally wouldn't recommend

[workingdataprep]

Yes, I'd like a column to differentiate actuals vs planned. Is there a way to do all of this?

[SundarRaj]

Hi @KarinSzilagyi, thanks for pointing that out!

Regards,