Transforming merged and centered excel files

[Anonymous]

Hello,

I am trying to transform a table in excel that has double headers.  I got rid of rows 1-5, and rows 13 and below but do not know how to deal with these sub headers

You can see in row 6 of the original image below there is a header for which bank the columns fall under.  Column C and D are bank 1 (the two columns are merged), E is 2, F is 3, and so on.

Original

I would like to be able to transform the data so that it appears as such:

Combining the appropriate bank # with the rows below it.

Thank you in advance for your help

[parry2k]

@Anonymous good to know, yes the core logic is there, just need to tweak the step and reference table. All the best!

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

[parry2k · Accepted solution]

@Anonymous here is the one file I used and you can take it from here:

let
    Source = Excel.Workbook(File.Contents("C:\Users\parvi\Downloads\anonymous weekly file.xlsx"), null, true),
    #"US Cash Detail_Sheet" = Source{[Item="US Cash Detail",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(#"US Cash Detail_Sheet", [PromoteAllScalars=true]),
    #"Removed Top Rows" = Table.Skip(#"Promoted Headers",4),
    #"Removed Other Columns" = Table.SelectColumns(#"Removed Top Rows",{"Company Weekly Cash Recap", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9"}),
    //get table for merged columns
    merge_row = Record.ToTable(#"Removed Other Columns"{0}),

    //fill the missing merge columns 
    fillmissing = Table.FillDown(merge_row,{"Value"}),

    //get column list for merge row
    merge_columns_aslist = fillmissing[Value],

    //get the actual header row
    header_row = Record.ToTable(#"Removed Other Columns"{1}),

    //get header row column list
    header_columns_aslist  = header_row[Value],

    //concatenate first and 2nd row list
    new_columns_aslist = List.Transform(List.Zip({merge_columns_aslist,header_columns_aslist}), each Text.Combine(_, " ") ),

    //remove top rows from the table
    remove_rows =  Table.RemoveFirstN(#"Removed Other Columns",2),
    
    //get current table column names
    current_columns_aslist = Table.ColumnNames(remove_rows),

    //create list of current column with new columns
    rename_column_aslist = List.Zip({current_columns_aslist,new_columns_aslist}),

    //rename columns in the final output table
    rename_columns = Table.RenameColumns(remove_rows, rename_column_aslist)

in
    rename_columns

[Anonymous]

I had to mess with it just a little bit but I finnally got it to work.  Thank you so much!

[parry2k]

@Anonymous can you share the pbix file with the sample excel you are using, remove sensitive information before sharing, it is a bit hard to find out what is going on and do the guess work.

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

[Anonymous]

I sent you an email 

[parry2k]

@Anonymous seems like you didn;t copy my full code, you are missing the comma in the line above

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

[Anonymous]

I did miss the comma, but now I am running into this error

It has something to do with the first column being a date, and the code trying to operate on it like it is text.

[parry2k]

@Anonymous this would be your full query:

let
    Source = Excel.Workbook(Parameter1, null, true),
    #"US Cash Detail_Sheet" = Source{[Item="US Cash Detail",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(#"US Cash Detail_Sheet", [PromoteAllScalars=true]),
    #"Removed Top Rows" = Table.Skip(#"Promoted Headers",4),
    #"Kept First Rows" = Table.FirstN(#"Removed Top Rows",3),
 
    //get table for merged columns
    merge_row = Record.ToTable(#"Kept First Rows"{0}),

    //fill the missing merge columns 
    fillmissing = Table.FillDown(merge_row,{"Value"}),

    //get column list for merge row
    merge_columns_aslist = fillmissing[Value],

    //get the actual header row
    header_row = Record.ToTable(Sheet1_Sheet{1}),

    //get header row column list
    header_columns_aslist  = header_row[Value],

    //concatenate first and 2nd row list
    new_columns_aslist = List.Transform(List.Zip({merge_columns_aslist,header_columns_aslist}), each Text.Combine(_, " ") ),

    //remove top rows from the table
    remove_rows =  Table.RemoveFirstN(Sheet1_Sheet,2),
    
    //get current table column names
    current_columns_aslist = Table.ColumnNames(remove_rows),

    //create list of current column with new columns
    rename_column_aslist = List.Zip({current_columns_aslist,new_columns_aslist}),

    //rename columns in the final output table
    rename_columns = Table.RenameColumns(remove_rows, rename_column_aslist)

in
    rename_columns

if you run into any issues, let me know.

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

[Anonymous]

@parry2k I am getting a "Token Comma Expected" error when using this code at the very first line.  

[parry2k]

@Anonymous after you have remove unwanted rows, add following steps:

let
    Source = Excel.Workbook(File.Contents("C:\Users\my\Downloads\Test Data.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],

    //get table for merged columns
    merge_row = Record.ToTable(Sheet1_Sheet{0}),

    //fill the missing merge columns 
    fillmissing = Table.FillDown(merge_row,{"Value"}),

    //get column list for merge row
    merge_columns_aslist = fillmissing[Value],

    //get the actual header row
    header_row = Record.ToTable(Sheet1_Sheet{1}),

    //get header row column list
    header_columns_aslist  = header_row[Value],

    //concatenate first and 2nd row list
    new_columns_aslist = List.Transform(List.Zip({merge_columns_aslist,header_columns_aslist}), each Text.Combine(_, " ") ),

    //remove top rows from the table
    remove_rows =  Table.RemoveFirstN(Sheet1_Sheet,2),
    
    //get current table column names
    current_columns_aslist = Table.ColumnNames(remove_rows),

    //create list of current column with new columns
    rename_column_aslist = List.Zip({current_columns_aslist,new_columns_aslist}),

    //rename columns in the final output table
    rename_columns = Table.RenameColumns(remove_rows, rename_column_aslist)

in
    rename_columns

also the sample excel sheet I used is attached, if you want to test, copy the attached file to a folder, change the path in the above script to read the file, start a new blank query, click advanced editor and copy the above code.

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

[Anonymous]

@parry2k thank you for your help but I don't understand where the code is supposed to fit in my existing code.  Below is my existing code, it is at the point where I have removed all the rows I do not want

let
    Source = Excel.Workbook(Parameter1, null, true),
    #"US Cash Detail_Sheet" = Source{[Item="US Cash Detail",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(#"US Cash Detail_Sheet", [PromoteAllScalars=true]),
    #"Removed Top Rows" = Table.Skip(#"Promoted Headers",4),
    #"Kept First Rows" = Table.FirstN(#"Removed Top Rows",3)
in
    #"Kept First Rows"