Combining multiple columns with the same header name

[Shamilton]

Thank you in advance for any help you might be able to give, I'm sure this is a simple issue but I am a novice and haven't been able to figure out how to resolve this.

I need to convert data that is formatted as image description briefs in Word into a useable data table in Excel. I've gotten as far as Power Query off of a .txt file, but I can't figure out how to convert the first column into headers and combining headers with the same name. The last image represents the desired result.

 

[alansidman]

I did not import from a word document, so you will have to change the source but this should give you the steps once you have your data in the two columns on how to transpose your data as you wish.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Filtered Rows" = Table.SelectRows(Source, each ([Column1] <> null)),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Column1"}, {{"Data", each _, type table [Column1=text, Column2=text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Data],"Index",1,1)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Column2", "Index"}, {"Custom.Column2", "Custom.Index"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Data"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Column1]), "Column1", "Custom.Column2"),
    #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Custom.Index"})
in
    #"Removed Columns1"

 

[Shamilton]

You are a lifesaver, thank you sir!

 

[alansidman]

Thanks for the feedback and you are welcome.