cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
YoussefN Frequent Visitor
Frequent Visitor

Multiple rows in different columns

Table2.jpgTable I wantTable1.jpgTable I have

Hi all,

 

I Have a large dataset that contains an object number and a location (place, area, street) In the dataset this is on line level  as you can see in the first table. But I want it structurend in different columns as shown in the seccond table. So that I have 4 columns.

I really would appreciate your help.

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: Multiple rows in different columns

Please see the comments in the code for the steps to be taken:

 

let
// Your source data Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VY2xCsJADEB/JdzcxbNfoAguddBJSodAgz1aErik+vveWYg6vpe8pO/DLjThIGrCYWg2PBJbJseriNKTFliQNxuLPcvqUeWbrDaBpvG70mG2xDAnfoCWi2Sf0f7/Y8UTqtXWRRt/i7aYC73gLnl20SFPaIbsJvLo1fAG", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Object = _t, Location = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Object", Int64.Type}, {"Location", type text}}), // Add Index column
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1), // Transform index column with modulo, value 3
#"Calculated Modulo" = Table.TransformColumns(#"Added Index", {{"Index", each Number.Mod(_, 3), type number}}), // Pivot on that new column, taking "Location" into values. If you do this by hand, make sure to disable aggregation in the advanced options ("Don't Aggregate".
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Calculated Modulo", {{"Index", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Calculated Modulo", {{"Index", type text}}, "en-US")[Index]), "Index", "Location") in #"Pivoted Column"

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

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




View solution in original post

Super User
Super User

Re: Multiple rows in different columns

Sure, just pivot on that column instead of the newly created and rename the resulting columns afterwards.

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

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




View solution in original post

10 REPLIES 10
Super User
Super User

Re: Multiple rows in different columns

Hmm...invoking @ImkeF

 

 


I have book! Learn Power BI from Packt


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

Proud to be a Datanaut!

Super User
Super User

Re: Multiple rows in different columns

Please see the comments in the code for the steps to be taken:

 

let
// Your source data Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VY2xCsJADEB/JdzcxbNfoAguddBJSodAgz1aErik+vveWYg6vpe8pO/DLjThIGrCYWg2PBJbJseriNKTFliQNxuLPcvqUeWbrDaBpvG70mG2xDAnfoCWi2Sf0f7/Y8UTqtXWRRt/i7aYC73gLnl20SFPaIbsJvLo1fAG", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Object = _t, Location = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Object", Int64.Type}, {"Location", type text}}), // Add Index column
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1), // Transform index column with modulo, value 3
#"Calculated Modulo" = Table.TransformColumns(#"Added Index", {{"Index", each Number.Mod(_, 3), type number}}), // Pivot on that new column, taking "Location" into values. If you do this by hand, make sure to disable aggregation in the advanced options ("Don't Aggregate".
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Calculated Modulo", {{"Index", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Calculated Modulo", {{"Index", type text}}, "en-US")[Index]), "Index", "Location") in #"Pivoted Column"

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

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




View solution in original post

YoussefN Frequent Visitor
Frequent Visitor

Re: Multiple rows in different columns

Hi Imke,

 

Thanx for your response, I am really  new to power BI and don't really understnad your awnser. Where do I have to put the code?

Can you walk me through a bit. maybe I did not gave enough information.

The table I have is from a Navision database. I used an odata feed to import the table into Power BI.

No I need to transfort it to a table with 4 columns.

Thanks in advance for your awnser

Super User
Super User

Re: Multiple rows in different columns

Hi Youssef,

Please check if the instructions in this video help you: How to integrate M-code into your solution

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

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




YoussefN Frequent Visitor
Frequent Visitor

Re: Multiple rows in different columns

Imke,

 

When I click the link I get: An Unexpected Error has occurred

Super User
Super User

Re: Multiple rows in different columns

YoussefN Frequent Visitor
Frequent Visitor

Re: Multiple rows in different columns

Ok thanx, I will check it out. I just checked the table in the original database and I saw that there is an extra column called code.

The code for City is always 00.000.0000, for area allways 10.000.0000 and for street allways 10.100.0000 as you can see in the picture.

Is there an easier way to do thiscolumn.jpgCode column

Super User
Super User

Re: Multiple rows in different columns

Sure, just pivot on that column instead of the newly created and rename the resulting columns afterwards.

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

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




View solution in original post

YoussefN Frequent Visitor
Frequent Visitor

Re: Multiple rows in different columns

If I pivot on the column code I get this result, I also tried the advanced optionsScreenHunter 283.jpgPivot

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 275 members 3,037 guests
Please welcome our newest community members: