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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Convert every 6 rows into columns

Hi guys!

 

Can anyone help me?

I have around 7.000 lines with data - a XML file.

Every 4 rows the "block" starts again. It's always "group, name, number, data"

Something like this:

 

Column1 - Column2

1234        - Group

XYZ         -  Name
ID           -  Number
May/20   -  Date

5678        - Group

ABC         -  Name
ID           -  Number
Apr/20   -  Date

1234        - Group

XYZ         -  Name
ID           -  Number
May/20   -  Date

 

I've tried to convert to columns by example but no success 😞

Any tips? 😄

1 ACCEPTED SOLUTION

Brazil is great, I'm helping you from Portugal! 😀

Sorry if I skipped some detailed steps...

You need to go here:

edugoncalves_0-1617822080152.png

After that, write the following code (for each column you gonna need), adapting your if-statement to each column.

E.g.:

Column Name = if [Column 2] = "Name" then [Column 1] else null
Column Id = if [Column 2] = "Id" then [Column 1] else null
...

 

edugoncalves_2-1617822356592.png

 

Um abraço! 😀

 

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

Thanks a lot! 🙂 🙂 Worked fine!! 😁

edugoncalves
Resolver I
Resolver I

Hi @Anonymous ,
I think I've found a receipt for this!
Used the following sample data:

edugoncalves_11-1617821159273.png

 

 

Then created 4 custom columns with the following code:
if [Column 2] = ##Column_to_be_created then [Column 1] else null

edugoncalves_12-1617821203324.png

 

 

After that, you need to fill-down the first custom column created and fill-up the last one and the penultimate

edugoncalves_13-1617821324530.png

 

edugoncalves_14-1617821350721.png

 

 

To finish, filter the third column to eliminate null values

edugoncalves_15-1617821381826.png

 

 

Note: This solution may not be feasible for other scenarios, and it is better to confirm that you do not "lose" data through the process...

 

Any questions, don't hesitate!

 

Best Regards,

Eduardo

 

[Edited because sample data was wrong.]

Anonymous
Not applicable

One more thing, @edugoncalves ...

I'm trying to remove the null's with your tip but I couldn't...lol
I have to put the Fill up and down after the code to add custom? I've tried shift + enter but returns me an error ....I could only fill down the first collumn but not the others 😞

 

Anonymous
Not applicable

BTW, @edugoncalves  in my original file I have 7 columns 

 

Anonymous
Not applicable

Hi Edu!
Greetings from Brazil 😉
I couldn't add the first line (if [Column 2] = ##Column_to_be_created then [Column 1] else null)
..I'm kind of newbie at PBI.

I've tried to insert your string at custom column but showed me "expected literal token" 😕

 

Brazil is great, I'm helping you from Portugal! 😀

Sorry if I skipped some detailed steps...

You need to go here:

edugoncalves_0-1617822080152.png

After that, write the following code (for each column you gonna need), adapting your if-statement to each column.

E.g.:

Column Name = if [Column 2] = "Name" then [Column 1] else null
Column Id = if [Column 2] = "Id" then [Column 1] else null
...

 

edugoncalves_2-1617822356592.png

 

Um abraço! 😀

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.