Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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:
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:
Hope this helps
Regards
User | Count |
---|---|
128 | |
108 | |
100 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |