Forum Discussion
Splitting every second value into different columns
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:
- Load single column table in your data model
- Add Index
- Add custom column which will hold the next value in your column1
- Ignore the error; it will be deleted when you filter your Column1 by the Header names you already know
- Remove Index after filtering the rows in Column1
- Pivot Column1 using Advanced Options: Don't Aggregate
Hope this helps
Regards
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...
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:
- Load single column table in your data model
- Add Index
- Add custom column which will hold the next value in your column1
- Ignore the error; it will be deleted when you filter your Column1 by the Header names you already know
- Remove Index after filtering the rows in Column1
- Pivot Column1 using Advanced Options: Don't Aggregate
Hope this helps
Regards