Forum Discussion
Solved
Multiple rows in different columns
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...
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"Sure, just pivot on that column instead of the newly created and rename the resulting columns afterwards.
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