Forum Discussion

OneWithQuestion's avatar
OneWithQuestion
Post Prodigy
3 years ago
Solved

Copy value from one column header to another?

I have this Excel file that we get from a 3rd party.    We have no ability to modify the file layout itself.   The issue is that the Excel file uses dual level headers, the top level is only pop...
Need Help
  • wdx223_Daniel's avatar
    wdx223_Daniel
    3 years ago

    firstly, import your file into Source without headers. ie, the columnnames will be "column 1","column2",.....

    secondly, click the "fx", left of the formula editor bar

    then use these code into the formula area.

    =let a=List.Skip(Table.ColumnNames(Source)) in Table.RenameColumns(Source,List.Zip({a,List.Accumulate(List.Skip(List.Zip(List.FirstN(Table.ToRows(Source),2))),{{},null},(x,y)=>{x{0}&{Text.Combine({y{0}??x{1},y{1}},"-")},y{0}??x{1}})}))

wdx223_Daniel's avatar
wdx223_Daniel
Community Champion
3 years ago

firstly, import your file into Source without headers. ie, the columnnames will be "column 1","column2",.....

secondly, click the "fx", left of the formula editor bar

then use these code into the formula area.

=let a=List.Skip(Table.ColumnNames(Source)) in Table.RenameColumns(Source,List.Zip({a,List.Accumulate(List.Skip(List.Zip(List.FirstN(Table.ToRows(Source),2))),{{},null},(x,y)=>{x{0}&{Text.Combine({y{0}??x{1},y{1}},"-")},y{0}??x{1}})}))