Splitting every second value into different columns

[Anonymous]

Hi - Is this possible:

I'm importing a lot of excel files from a folder - On the first sheet, there is information about the content of the file that I need in a table. The problem is that the column header and value is in the same column:

I have:

Column1:

Headertext1

Value

Headertext2

Value

Headertext3

Value

Headertext4

Value

Headertext1

Value

ect...

And I want it to be imported to:

Headertext1   Headertext2   Headertext3   Headertext4

Value              Value              Value             Value

Value             ect...

I will be able to hard code the names of the headers, so eg... every time the header-name is found the value in the next row should then be placed into the right column.

Hope you can help

Kent

[prateekraina]

Hi @Anonymous

Check this out. It might help.

@prateekraina

[Anonymous]

Thanks @prateekraina, I was looking into unpivoting, but it doses not look like it handles issues where I have future column names and values in every second row.

Somehow I need to tell the query that when it sees "Headline1" that should be a new column and the value in the next row should then be the value...

[omrdmr · Accepted solution]

Hi @Anonymous,

I've demonstrated how you could get what you need in the below .pbix file

https://1drv.ms/u/s!Avvfp0eWFJJDgi-8iU6ruqY48sDD

It should do trick as long as you know how to differentiate values from headers. So, do the following steps:

1. Load single column table in your data model
2. Add Index
3. Add custom column which will hold the next value in your column1
4. Ignore the error; it will be deleted when you filter your Column1 by the Header names you already know
5. Remove Index after filtering the rows in Column1
6. Pivot Column1 using Advanced Options: Don't Aggregate

Hope this helps

Regards

[Anonymous]

WONDERFUL! @omrdmr. Works like s charm

Thank you so much!

Kent