Multiple rows in different columns

[YoussefN]

Table I wantTable 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.

[ImkeF · Accepted solution]

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"

[Ashish_Mathur]

Hi,

You may refer to my solution here.

Hope this helps.

[Greg_Deckler]

Hmm...invoking @ImkeF

[ImkeF · Accepted solution]

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"

[YoussefN]

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

[ImkeF]

Hi Youssef,

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

[YoussefN]

Imke,

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

[ImkeF]

Sorry, please take this link:

https://community.powerbi.com/t5/Webinars-and-Video-Gallery/Power-BI-Forum-Help-How-to-integrate-M-c...

[YoussefN]

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 thisCode column

[ImkeF · Accepted solution]

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

[YoussefN]

If I pivot on the column code I get this result, I also tried the advanced optionsPivot