Help with Data Transformation/Clean up - flattening header rows

[anandav]

Hi,

I have an Excel report with some header data. I am familiar with basic transformation such as removing header rows, pivoting, fill up and down, etc.

But I need to tranform the header informatikon into a columns.

Source:

https://1drv.ms/x/s!AgWALQ1qUAnPalTYohAklczFEkg

 

Transformation I need to achieve in Power BI (Power Query GUI/ M):

I can do the remaining fill down, etc. from here, but need help to get the header info into columns.

Thanks in advance.

[CheenuSing · Accepted solution]

Hi @anandav

You can try the following power query to do the transformation

let
    Source = Excel.Workbook(File.Contents("C:\PowerBICommunity\ExcelSchedules - Copy.xlsx"), null, true),
    Source_Sheet = Source{[Item="Source",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Source_Sheet,{{"Column1", type text}, {"Column2", type any}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}, {"Column10", type any}, {"Column11", type any}, {"Column12", type any}, {"Column13", type any}}),
    #"Kept First Rows" = Table.FirstN(#"Changed Type",6),
    #"Removed Columns" = Table.RemoveColumns(#"Kept First Rows",{"Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13"}),
    #"Sorted Rows" = Table.Sort(#"Removed Columns",{{"Column1", Order.Ascending}}),
    #"Removed Top Rows" = Table.Skip(#"Sorted Rows",1),
    #"Transposed Table" = Table.Transpose(#"Removed Top Rows"),
    #"PromoteHeader" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Source1" = Table.AddIndexColumn(PromoteHeader, "Index", 1, 1),
    #"RemovedTopRows7" = Table.Skip(#"Changed Type",7),
    #"PromotedHeaders" = Table.PromoteHeaders(RemovedTopRows7, [PromoteAllScalars=true]),
    #"Added Index" = Table.AddIndexColumn(#"PromotedHeaders", "Index", 1, 1),
    #"Merged Queries" = Table.NestedJoin(#"Added Index",{"Index"},#"Source1",{"Index"},"Source1",JoinKind.LeftOuter),
    #"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries", "Source1", {"Document Type", "Last Modified", "Period From", "Period To", "Unit", "Index"}, {"Source1.Document Type", "Source1.Last Modified", "Source1.Period From", "Source1.Period To", "Source1.Unit", "Added Index1.Index"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Added Index1",{{"Source1.Document Type", "Document Type"}, {"Source1.Last Modified", "Last Modified"}, {"Source1.Period From", "Period From"}, {"Source1.Period To", "Period To"}, {"Source1.Unit", "Unit"}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns",{"Added Index1.Index", "Index"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns1",{"Document Type", "Last Modified", "Period From", "Period To", "Unit", "Resource Name", "Work Type", "Work/Project Name", "Work/Project Code", "JIRA Num", "Description", "Priority", "Start Date", "End Date", "Estmate Period Start Date", "Estmate Period End Date", "Estimate Period Hours", "Remaining Period Hours"})
in
    #"Reordered Columns"

Let me know if you need further help.

If this work for you please accept this as a solution and also give KUDOS.

[anandav]

Hi @CheenuSing,

Thanks a lot for your help. Sorry for the late reply as I was unwell and could not test your solution earlier.

It works perfectly!

Thanks a lot for taking time to go through the file and posting a detail script.

After reviewing your code, I manged to create two queries - one for transposing header details and another for detail rows - and then merged the twp quries to achieve the same results via the GUI. The downside with GUI is that it creates 3 tables - transposed header details, details rows and the final query (as I merged it as a new query). This may not be memmory/storage efficient for large tables but in my case it is acceptable. I couldn't have done without your code.

Also it has encouraged me to learn Power Query / M !

Once again thanks a lot for saving my day.

[CheenuSing]

Hi @anandav

This may be achievable using power query.

Do you want the rest of the rows also to be filled up by the first 5 columns.

Please clarify.

Cheers

CheenuSing