Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

1 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

View solution in original post

4 REPLIES 4
prateekraina
Memorable Member
Memorable Member

Hi @Anonymous

 

Check this out. It might help.

 

@prateekraina

Anonymous
Not applicable

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:

 

  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
Not applicable

WONDERFUL! @omrdmr. Works like s charm

 

Thank you so much!

 

Kent

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.