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

• Edited by Tobias Weysters 23 hours 28 minutes ago

Hi Tobias,

not sure if I got your request right, but try the following: Add an index column and perform an "Unpivot others" operation on it. This will create 2 columns where "US" & "Internal" will stay side to side. Now merge both columns and select your separator. Then filter on the rows that don't make sense & unpivot again.

This solution will cover your other aspects as well.