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
Bbrown44
Advocate II
Advocate II

convert 1 column into 4 columns

Hello,

 

I have data in one row that consist of 4 variables.

 

What I want is

Every 1st row = Column A

Every 2nd row = column B

Every 3rd row = column c

Every 4th row = column D

 

So when you get to the 5th row you loop back to

 

5th row = Column A

 6throw = column B

7th row = column c

8th row = column D

 

And so on in this loop until the rows of data are exhausted.

 

See image below as for the structure of data

 

 

change to transform into 4 variables all rows v2.png

 

1 ACCEPTED SOLUTION

Hi @Bbrown44 ,

 

Check this file: Download PBIX 

 

Ricardo

 



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



View solution in original post

5 REPLIES 5
az38
Community Champion
Community Champion

Hi @Bbrown44 

try a column

Column = SWITCH(TRUE(),
MOD([Index], 4) = 0, "Column A", 
MOD([Index], 4) = 1, "Column B", 
MOD([Index], 4) = 2, "Column C", 
MOD([Index], 4) = 3, "Column D", 
BLANK())

do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn
amitchandak
Super User
Super User

the Pivot default does not recognize that the data needs to be structure into 4 columns

 

it pivots all the data long.  so then you have an infinite number of columns from the 1 column.

Hi @Bbrown44 ,

 

Check this file: Download PBIX 

 

Ricardo

 



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



Thanks!

 

this works as well.

 

I was able to find a tutorial to solve this as well.

 

https://www.youtube.com/watch?v=V_ULyeHNJFY 

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.