Forum Discussion
Excel matrix-style source data for Power BI
Hello All!
I recently started working for new company trying to utilize Power BI as a intelligence tool and my first little project has me somewhat stumped. It feels like I should have figured it out by now with this seemingly being a known issue, but if any of you can guide me to tutorials, videos, instructions, I would be forever grateful.
I have a matrix-style excel sheet that is a bit complex, across the top are date entities then sectioned into three categories Minor/Major/Loss Rate. I've attached some sample source data (modified for sensitivity) and a picture of the data structure. I've tried every transpose, unpivot, fill down even Keying structure to try and get a tabular view for power bi analysis. As far as the final output I want to leave it open in order to not box in any ideas as I need to be able to recreate Time intelligence (Line charts, Bar) visuals that have already been achieved. And then supplement with more meaningful visuals and calculations and conditional formatting to really showcase PBI capabilites. I would need the full break down of steps in order to repeat on the raw data unedited data.
------------------------------
Marcus S
Business Intelligence Analyst
------------------------------
Try the following steps.
1. “Transpose Table”
2. With Column1 highlighted, select “Fill Down”
3. With Column1 and Column2 highlighted, select “Merge Columns” with Colon as separator.
4. “Transpose Table”
5. “Promote Headers”
6. With ":” column hightlighted, “Unpivot Other Columns”
7. With “Attribute” column highlighted, “Split Column” with Colon as delimiter.
8. Rename columns.let Source = Excel.Workbook( File.Contents( "C:\Power_BI\PBI Community\Excel matrix-style source data for Power BI\MatrixData.xlsx" ), null, true ), Sheet1_Sheet = Source{[Item = "Sheet1", Kind = "Sheet"]}[Data], #"Changed Type" = Table.TransformColumnTypes( Sheet1_Sheet, { {"Column1", type text}, {"Column2", type any}, {"Column3", type any}, {"Column4", type any}, {"Column5", type any}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}, {"Column10", type any}, {"Column11", type any}, {"Column12", type any}, {"Column13", type any} } ), #"Transposed Table" = Table.Transpose(#"Changed Type"), #"Filled Down" = Table.FillDown(#"Transposed Table", {"Column1"}), #"Merged Columns" = Table.CombineColumns( Table.TransformColumnTypes(#"Filled Down", {{"Column1", type text}}, "en-US"), {"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 text}, {"1/1/2022:Minor Deviation Rate", type number}, {"1/1/2022:Major Deviation Rate", Int64.Type}, {"1/1/2022:Loss Rate", type number}, {"2/1/2022:Minor Deviation Rate", type number}, {"2/1/2022:Major Deviation Rate", Int64.Type}, {"2/1/2022:Loss Rate", type number}, {"3/1/2022:Minor Deviation Rate", type number}, {"3/1/2022:Major Deviation Rate", Int64.Type}, {"3/1/2022:Loss Rate", type number}, {"4/1/2022:Minor Deviation Rate", type number}, {"4/1/2022:Major Deviation Rate", Int64.Type}, {"4/1/2022:Loss Rate", type number} } ), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns( #"Changed Type1", {":"}, "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 date}, {"Attribute.2", type text}} ), #"Renamed Columns" = Table.RenameColumns( #"Changed Type2", {{":", "Center"}, {"Attribute.1", "Period"}, {"Attribute.2", "CalcType"}} ) in #"Renamed Columns"EDITED TO ADD:
I realize this probably isn't the format you want. It would help if you could define your expected output.
Until then, I'm going to try to find a way to have the CalcTypes in separate columns. I'm kinda stuck right now but will see if I can give you that as an option.
6 Replies
Also what is the supported file types on this platform? Having trouble attaching sample files. Thanks so much again!
Try the following steps.
1. “Transpose Table”
2. With Column1 highlighted, select “Fill Down”
3. With Column1 and Column2 highlighted, select “Merge Columns” with Colon as separator.
4. “Transpose Table”
5. “Promote Headers”
6. With ":” column hightlighted, “Unpivot Other Columns”
7. With “Attribute” column highlighted, “Split Column” with Colon as delimiter.
8. Rename columns.let Source = Excel.Workbook( File.Contents( "C:\Power_BI\PBI Community\Excel matrix-style source data for Power BI\MatrixData.xlsx" ), null, true ), Sheet1_Sheet = Source{[Item = "Sheet1", Kind = "Sheet"]}[Data], #"Changed Type" = Table.TransformColumnTypes( Sheet1_Sheet, { {"Column1", type text}, {"Column2", type any}, {"Column3", type any}, {"Column4", type any}, {"Column5", type any}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}, {"Column10", type any}, {"Column11", type any}, {"Column12", type any}, {"Column13", type any} } ), #"Transposed Table" = Table.Transpose(#"Changed Type"), #"Filled Down" = Table.FillDown(#"Transposed Table", {"Column1"}), #"Merged Columns" = Table.CombineColumns( Table.TransformColumnTypes(#"Filled Down", {{"Column1", type text}}, "en-US"), {"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 text}, {"1/1/2022:Minor Deviation Rate", type number}, {"1/1/2022:Major Deviation Rate", Int64.Type}, {"1/1/2022:Loss Rate", type number}, {"2/1/2022:Minor Deviation Rate", type number}, {"2/1/2022:Major Deviation Rate", Int64.Type}, {"2/1/2022:Loss Rate", type number}, {"3/1/2022:Minor Deviation Rate", type number}, {"3/1/2022:Major Deviation Rate", Int64.Type}, {"3/1/2022:Loss Rate", type number}, {"4/1/2022:Minor Deviation Rate", type number}, {"4/1/2022:Major Deviation Rate", Int64.Type}, {"4/1/2022:Loss Rate", type number} } ), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns( #"Changed Type1", {":"}, "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 date}, {"Attribute.2", type text}} ), #"Renamed Columns" = Table.RenameColumns( #"Changed Type2", {{":", "Center"}, {"Attribute.1", "Period"}, {"Attribute.2", "CalcType"}} ) in #"Renamed Columns"EDITED TO ADD:
I realize this probably isn't the format you want. It would help if you could define your expected output.
Until then, I'm going to try to find a way to have the CalcTypes in separate columns. I'm kinda stuck right now but will see if I can give you that as an option.
After the steps mentioned above, add 1 more step:
9. While "CalcType" column is highlighted, "Pivot Column" using "Value" as the "Values Column".
Thank you! It gets me really close! Did not think to merge the dates to the centers.
The output comes to look like this. Which I'm pretty sure I can do Time Intelligence and other visuals. But if you continue to find a better format for analysis please don't hesitate to share
That last step is even better!
