Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
anandav
Skilled Sharer
Skilled Sharer

Help with Data Transformation/Clean up - flattening header rows

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

 

 Source.JPG

 

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

Result.JPG

 

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

 

Thanks in advance.

1 ACCEPTED SOLUTION
CheenuSing
Community Champion
Community Champion

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.

 

 

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

View solution in original post

3 REPLIES 3
CheenuSing
Community Champion
Community Champion

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.

 

 

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

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! Smiley Happy

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
Community Champion
Community Champion

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

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.