Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to 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"
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Sure, just pivot on that column instead of the newly created and rename the resulting columns afterwards.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hi,
You may refer to my solution here.
Hope this helps.
Hmm...invoking @ImkeF
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"
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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
Hi Youssef,
Please check if the instructions in this video help you: How to integrate M-code into your solution
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Imke,
When I click the link I get: An Unexpected Error has occurred
Sorry, please take this link:
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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 this
Sure, just pivot on that column instead of the newly created and rename the resulting columns afterwards.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
If I pivot on the column code I get this result, I also tried the advanced options
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |