• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Struggling to import an Excel data file with merged rows and headings correctly into Power BI

Raylou

New Member
I hope this question finds you well.



I have an excel file and there are merged headings and rows in it. I have been struggling the whole day to import this file, using transpose, unpivot, merging etc. without success. I have even tried rearranging the data but that is not feasible. I would like to get the file imported correctly so that I can filter the data for the quarters in a table and in graphs. I also want slicers to show certain columns if needed for the descriptions etc. Also want to filter per quarters.



Need to filter by Key Performance Areas, and Quarters for the various areas, like ditribution, transmissio, etc.



It seem straight forward, but with my little experience I cannot get it in the correct way.



Any help would be appreciated. File attached. Thanks!
 

Attachments

  • Dashboard FY202324_v0.6.xlsx
    26.7 KB · Views: 4
Something like (quick & dirty)

Code:
let
    Source = Excel.Workbook(File.Contents("G:\Uploads\Dashboard FY202324_v0.6.xlsx"), null, true),
    #"Security Divisional KPAs _Sheet" = Source{[Item="Security Divisional KPAs ",Kind="Sheet"]}[Data],
    #"Removed Top Rows" = Table.Skip(#"Security Divisional KPAs _Sheet",1),
    #"Filled Down" = Table.FillDown(#"Removed Top Rows",{"Column1"}),
    #"Transposed Table" = Table.Transpose(#"Filled Down"),
    #"Filled Down1" = Table.FillDown(#"Transposed Table",{"Column1"}),
    #"Merged Columns" = Table.CombineColumns(#"Filled Down1",{"Column1", "Column2"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"Merged"),
    #"Transposed Table1" = Table.Transpose(#"Merged Columns"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"|Key Performance Areas", type text}, {"|Security KPI's                                                            ", type text}, {"|Definition & Measure", type text}, {"|Source of evidence ", type text}, {"Quarter 1|Distribution", type number}, {"Quarter 1|Generations", type number}, {"Quarter 1|Transmission", type number}, {"Quarter 1|ERE", type number}, {"Quarter 1|ERI", type number}, {"Quarter 2|Distribution", Int64.Type}, {"Quarter 2|Generations", Int64.Type}, {"Quarter 2|Transmission", Int64.Type}, {"Quarter 2|ERE", Int64.Type}, {"Quarter 2|ERI", Int64.Type}, {"Quarter 3|Distribution", Int64.Type}, {"Quarter 3|Generations", Int64.Type}, {"Quarter 3|Transmission", Int64.Type}, {"Quarter 3|ERE", Int64.Type}, {"Quarter 3|ERI", Int64.Type}, {"Quarter 4|Distribution", Int64.Type}, {"Quarter 4|Generations", Int64.Type}, {"Quarter 4|Transmission", Int64.Type}, {"Quarter 4|ERE", Int64.Type}, {"Quarter 4|ERI", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"|Key Performance Areas", "|Security KPI's                                                            ", "|Definition & Measure", "|Source of evidence "}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByEachDelimiter({"|"}, QuoteStyle.Csv, false), {"Quarters", "Label"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Quarters", type text}, {"Label", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",List.Zip({Table.ColumnNames(#"Changed Type1"), List.Transform(Table.ColumnNames(#"Changed Type1"), each if Text.Contains(_, "|") then Text.AfterDelimiter(_, "|") else _)}) )
in
    #"Renamed Columns"
 

Attachments

  • Solution_Dashboard FY202324_v0.6.xlsx
    39.8 KB · Views: 3

they look nice for reports but create lots of issues as you have found out. Probably the best thing to do is unmerge the cells and then do fills to replicate the information if critical.
Good Morning Alan, yes they do. Problem is for the people needing to capture the info and the other sheets I will develop that will feed the percentages into this sheet. Then it would look a bit crazy to them with data in the excel that should fit Power BI properly if I make sense. :)
 
Something like (quick & dirty)

Code:
let
    Source = Excel.Workbook(File.Contents("G:\Uploads\Dashboard FY202324_v0.6.xlsx"), null, true),
    #"Security Divisional KPAs _Sheet" = Source{[Item="Security Divisional KPAs ",Kind="Sheet"]}[Data],
    #"Removed Top Rows" = Table.Skip(#"Security Divisional KPAs _Sheet",1),
    #"Filled Down" = Table.FillDown(#"Removed Top Rows",{"Column1"}),
    #"Transposed Table" = Table.Transpose(#"Filled Down"),
    #"Filled Down1" = Table.FillDown(#"Transposed Table",{"Column1"}),
    #"Merged Columns" = Table.CombineColumns(#"Filled Down1",{"Column1", "Column2"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"Merged"),
    #"Transposed Table1" = Table.Transpose(#"Merged Columns"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"|Key Performance Areas", type text}, {"|Security KPI's                                                            ", type text}, {"|Definition & Measure", type text}, {"|Source of evidence ", type text}, {"Quarter 1|Distribution", type number}, {"Quarter 1|Generations", type number}, {"Quarter 1|Transmission", type number}, {"Quarter 1|ERE", type number}, {"Quarter 1|ERI", type number}, {"Quarter 2|Distribution", Int64.Type}, {"Quarter 2|Generations", Int64.Type}, {"Quarter 2|Transmission", Int64.Type}, {"Quarter 2|ERE", Int64.Type}, {"Quarter 2|ERI", Int64.Type}, {"Quarter 3|Distribution", Int64.Type}, {"Quarter 3|Generations", Int64.Type}, {"Quarter 3|Transmission", Int64.Type}, {"Quarter 3|ERE", Int64.Type}, {"Quarter 3|ERI", Int64.Type}, {"Quarter 4|Distribution", Int64.Type}, {"Quarter 4|Generations", Int64.Type}, {"Quarter 4|Transmission", Int64.Type}, {"Quarter 4|ERE", Int64.Type}, {"Quarter 4|ERI", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"|Key Performance Areas", "|Security KPI's                                                            ", "|Definition & Measure", "|Source of evidence "}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByEachDelimiter({"|"}, QuoteStyle.Csv, false), {"Quarters", "Label"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Quarters", type text}, {"Label", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",List.Zip({Table.ColumnNames(#"Changed Type1"), List.Transform(Table.ColumnNames(#"Changed Type1"), each if Text.Contains(_, "|") then Text.AfterDelimiter(_, "|") else _)}) )
in
    #"Renamed Columns"
Good Morning,

Thank you so much for attempting to help me. to be honest this is going over my head not 100% sure how to add this to the Power BI model. Hehe. I have tried to attach the Power BI file as well, but see I cannot. :( Tried a few thing, but not getting there. May you please guide me how to add the code. I assume copy and past does not work although I have changed the path of my file. Thanks!
 
Actually, copy and paste while adapting the file path should work. Except for the last step, I simply used the UI.
I can only advise to study it in Excel first and see for each of the steps how it is working.
 
Good Morning,

Thank you so much for attempting to help me. to be honest this is going over my head not 100% sure how to add this to the Power BI model. Hehe. I have tried to attach the Power BI file as well, but see I cannot. :( Tried a few thing, but not getting there. May you please guide me how to add the code. I assume copy and past does not work although I have changed the path of my file. Thanks!
Okay, I see now where I need to copy and paste it in the Advance Editor. :) Let me try.....
 
Actually, copy and paste while adapting the file path should work. Except for the last step, I simply used the UI.
I can only advise to study it in Excel first and see for each of the steps how it is working.
Cool, thanks a million, I managed to get those steps in, and it looks like I can try and work with it in this format! Thank you very much for pointing me in the right direction! Much appreciated! Have a great day! :)
 
Actually, copy and paste while adapting the file path should work. Except for the last step, I simply used the UI.
I can only advise to study it in Excel first and see for each of the steps how it is working.
Hi there is there anyway to let change the heading of the columns to show Quarter 1, Quarter 2, Quarter 3, Quarter 4, rather than just Quarters. Currently as per the attached table via it looks like this. The aim is that all the Key Performance Areas, Security KPI's etc. will be following in sequence next to each other with there percentages for easy reading and comparison. Hope I make sense. Like the excel via basically looked. Thanks!
 

Attachments

  • Table headings options.JPG
    Table headings options.JPG
    93.3 KB · Views: 7
Back
Top