Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Splitting every second value into different columns

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

Need Help
  • omrdmr's avatar
    omrdmr
    9 years ago

    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

4 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      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's avatar
        omrdmr
        Helper I
        9 years ago

        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