Renaming Cells or Column Headers in a loop with Power Query

Hey there,

I am using Power Query for process some of my access logs for several countries.

Currently, the first row is showing several country abbreviations each seperated by an empty cell (For Example "US" "null" "UK" "null" "DE" etc.). The row beneath repeats the two values "Internal" and "External" all the time.

What I need to do is to change the second row to combine both rows ("US_Internal", "US_External", "UK_Internal", "UK_External" and so on).

There is no problem in doing this manually, but as there are about 100 countries, I wanna do this automatically. Another Problem is, that the amount of countries changes as only countries with at least on access are listed!

Can anyone help me out?

Thanks a lot!

Toby


September 4th, 2015 7:27am

Hey Imke,

thanks for your fast reply. For a better understanding, I just took some screenshots.

This is my input:

And I need to transform it so something like that:

When trying your solution, I get a three column table like that:

  

I don't get how to transform this to my wished transformation.

I hope you can help me with that!

Thanks in advance!

Tobias

Free Windows Admin Tool Kit Click here and download it now
September 9th, 2015 2:53am

Sorry Tobias,

you need the transpose-Operation instead of the unpivot-&Pivot-back-steps:

let
    Source = Excel.CurrentWorkbook(){[Name="Tabelle2"]}[Content],
    Transpose = Table.Transpose(Source),
    FillDown = Table.FillDown(Transpose,{"Column1"}),
    MergeHeaders = Table.CombineColumns(FillDown,{"Column1", "Column2"},Combiner.CombineTextByDelimiter("_", QuoteStyle.None),"Merged"),
    TransposBack = Table.Transpose(MergeHeaders),
    PromoteHeaders = Table.PromoteHeaders(TransposBack)
in
    PromoteHeaders

LinkToFi

September 9th, 2015 3:57am

Thanks a lot, that solution works perfectly!
Free Windows Admin Tool Kit Click here and download it now
September 10th, 2015 2:49am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics