Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I'm new to M language. Your help with me converting the row data to column data or producing an M-equivalent code to the DAX formula is very much appreciated.
I have a set of row data that contains user input: Feedback ID, question, and answer. Each feedback ID contains only one "Name", one "Date", one or more entries of "Product Purhased" and "Serial Number" entries in the "Question" column. The sequence of Product Purchased correspond to that of Serial Number. See the data sample below.
Feedback ID | Question | Answer |
2020001 | Name | Adam |
2020001 | Date | 10-Jan-20 |
2020001 | Product Purchased | Item 1 |
2020001 | Product Purchased | Item 2 |
2020001 | Product Purchased | Item 3 |
2020001 | Serial Number | AMD3060788 |
2020001 | Serial Number | VQS4567300 |
2020001 | Serial Number | ITQ0063597 |
2020002 | Name | Jenny |
2020002 | Date | 15-Jan-20 |
2020002 | Product Purchased | Item 15 |
2020002 | Product Purchased | Item 16 |
2020002 | Serial Number | AMD3060789 |
2020002 | Serial Number | TQM7760385 |
What I'd like to achieve is convert this data to a table with each row containing all the information for each purchase (Name, Date, Product Purchased, Serial Number). I did this in DAX. However, the data is refreshing frequently, and I'd like to automate this process somehow. This is kind of what I'd like to achieve:
Row ID | Name | Date | Product Purchased | Serial Number |
20200011 | Adam | 10-Jan-20 | Item 1 | AMD3060788 |
20200012 | Adam | 10-Jan-20 | Item 2 | VQS4567300 |
20200013 | Adam | 10-Jan-20 | Item 3 | ITQ0063597 |
20200021 | Jenny | 15-Jan-20 | Item 15 | AMD3060789 |
20200022 | Jenny | 15-Jan-20 | Item 16 | TQM7760385 |
To do this, I have done the following steps:
1. Create a column ColIndex in Table1 by adding an index column to this data;
2. In DAX I created a column "Multiple Entry Index" using the following formula:
Multilple Entry Index = CALCULATE(COUNT([ColIndex]), ALL('Table1'),
Feedback ID | Question | Answer | Multilple Entry Index | ColIndex | Row ID |
2020001 | Name | Adam | 1 | 1 | 20200011 |
2020001 | Date | 10-Jan-20 | 1 | 2 | 20200011 |
2020001 | Product Purchased | Item 1 | 1 | 3 | 20200011 |
2020001 | Product Purchased | Item 2 | 2 | 4 | 20200012 |
2020001 | Product Purchased | Item 3 | 3 | 5 | 20200013 |
2020001 | Serial Number | AMD3060788 | 1 | 6 | 20200011 |
2020001 | Serial Number | VQS4567300 | 2 | 7 | 20200012 |
2020001 | Serial Number | ITQ0063597 | 3 | 8 | 20200013 |
2020002 | Name | Jenny | 1 | 9 | 20200021 |
2020002 | Date | 15-Jan-20 | 1 | 10 | 20200021 |
2020002 | Product Purchased | Item 15 | 1 | 11 | 20200021 |
2020002 | Product Purchased | Item 16 | 2 | 12 | 20200022 |
2020002 | Serial Number | AMD3060789 | 1 | 13 | 20200021 |
2020002 | Serial Number | TQM7760385 | 2 | 14 | 20200022 |
4. I removed Feedback ID, Multiple Entry Index, and ColIndex.
5. I pivoted Column Question with values from Answer column.
6. I filled down null records.
The challenging part for me is to generate the Multi Entry Index similar to DAX column in M...
____________________________Query Settings in Table 1_________________________________
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZCxCoMwEEB/RTIrnEmT6FhwUVAUpYs4pBpoobGQJkP/vjqUSkprpsC9B3d5fY8wYACIUYgqoeTyHCeh0BBuSSbMSmKICjFHGBxc6/tkRxPUVo8X8ZDTMsuNVEHsK2JfkThiK/VV3ILKqrPU6/FlRoABT5Id8dS0B8o4Afczrph3DQAjNOUbEX9yFXKenw5696LfvfDfXtTbZI75M0S6I3ZNyTkDkiy7hxc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Feedback ID" = _t, Question = _t, Answer = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Feedback ID", Int64.Type}, {"Question", type text}, {"Answer", type text}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
#"Renamed Columns" = Table.RenameColumns(#"Added Index",{{"Index", "ColIndex"}})
in
#"Renamed Columns"
__________________________________________End of Query Settings in Table1___________________________________________________________
________________________________________Query Settings in Table 2_________________________________
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZJRC4IwFIX/iviscHfnNn0MeilIkqKX8GHpoCANTB/69xncZY0G4cO4sJ2Pc7hnx2OIgADAwijMdWPGsah1Mw5Gh95ZWEaf4qXuX2IG8Vq3MYJV+4ltd6uHqg+2Q1ed9d3U492qN01gnfgMFskzmVj8l+XkKSaWO+zOdBd9DfKhOZnutZvNkoMElaaUWfozu+yh2CVCKg5AmZU/s8uu9gWA5CJTlDn9nRmnGtembR+UMnurkTlq26P47pGBH/EXKSzN5tCS9sKmT4To0N5CMmvN/dYuvC82SkngqbDOyZdz+QQ=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Feedback ID" = _t, Question = _t, Answer = _t, #"Multilple Entry Index" = _t, ColIndex = _t, #"Row ID" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Feedback ID", Int64.Type}, {"Question", type text}, {"Answer", type text}, {"Multilple Entry Index", Int64.Type}, {"ColIndex", Int64.Type}, {"Row ID", Int64.Type}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Feedback ID", "Multilple Entry Index", "ColIndex"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Question]), "Question", "Answer"),
#"Filled Down" = Table.FillDown(#"Pivoted Column",{"Name", "Date"})
in
#"Filled Down"
____________________________________________________End of Query Settings in Table2_____________________________________________________
Solved! Go to Solution.
Thanks for including the M code, as it made it easy to copy/paste and work on it. This was a fun one. Here is some M code that I think does what you are looking for.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZCxCoMwEEB/RTIrnEmT6FhwUVAUpYs4pBpoobGQJkP/vjqUSkprpsC9B3d5fY8wYACIUYgqoeTyHCeh0BBuSSbMSmKICjFHGBxc6/tkRxPUVo8X8ZDTMsuNVEHsK2JfkThiK/VV3ILKqrPU6/FlRoABT5Id8dS0B8o4Afczrph3DQAjNOUbEX9yFXKenw5696LfvfDfXtTbZI75M0S6I3ZNyTkDkiy7hxc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Feedback ID" = _t, Question = _t, Answer = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Feedback ID", Int64.Type}, {"Question", type text}, {"Answer", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Feedback ID", "Question"}, {{"AllRows", each _, type table [Feedback ID=number, Question=text, Answer=text]}}),
#"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[Question]), "Question", "AllRows"),
#"Expanded Name" = Table.ExpandTableColumn(#"Pivoted Column", "Name", {"Answer"}, {"Answer"}),
#"Expanded Date" = Table.ExpandTableColumn(#"Expanded Name", "Date", {"Answer"}, {"Answer.1"}),
ExtractList1 = Table.TransformColumns(#"Expanded Date", {{"Product Purchased", each _[Answer]}}),
ExtractList2 = Table.TransformColumns(ExtractList1, {{"Serial Number", each _[Answer]}}),
#"Added Custom" = Table.AddColumn(ExtractList2, "NewTable", each Table.FromColumns({[Product Purchased], [Serial Number]})),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "WithIndex", each Table.AddIndexColumn([NewTable],"Index",1, 1)),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Product Purchased", "Serial Number", "NewTable"}),
#"Expanded WithIndex" = Table.ExpandTableColumn(#"Removed Columns", "WithIndex", {"Column1", "Column2", "Index"}, {"Column1", "Column2", "Index"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded WithIndex",{{"Answer", "Name"}, {"Answer.1", "Date"}, {"Column1", "Product Purchased"}, {"Column2", "Serial Number"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Serial Number", type text}, {"Product Purchased", type text}, {"Feedback ID", type text}, {"Index", type text}}),
#"Merged Columns" = Table.CombineColumns(#"Changed Type1",{"Feedback ID", "Index"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"RowID"),
#"Reordered Columns" = Table.ReorderColumns(#"Merged Columns",{"RowID", "Name", "Date", "Product Purchased", "Serial Number"})
in
#"Reordered Columns"
If this works for you, please mark it as the solution. Kudos are also appreciated. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Thanks for including the M code, as it made it easy to copy/paste and work on it. This was a fun one. Here is some M code that I think does what you are looking for.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZCxCoMwEEB/RTIrnEmT6FhwUVAUpYs4pBpoobGQJkP/vjqUSkprpsC9B3d5fY8wYACIUYgqoeTyHCeh0BBuSSbMSmKICjFHGBxc6/tkRxPUVo8X8ZDTMsuNVEHsK2JfkThiK/VV3ILKqrPU6/FlRoABT5Id8dS0B8o4Afczrph3DQAjNOUbEX9yFXKenw5696LfvfDfXtTbZI75M0S6I3ZNyTkDkiy7hxc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Feedback ID" = _t, Question = _t, Answer = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Feedback ID", Int64.Type}, {"Question", type text}, {"Answer", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Feedback ID", "Question"}, {{"AllRows", each _, type table [Feedback ID=number, Question=text, Answer=text]}}),
#"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[Question]), "Question", "AllRows"),
#"Expanded Name" = Table.ExpandTableColumn(#"Pivoted Column", "Name", {"Answer"}, {"Answer"}),
#"Expanded Date" = Table.ExpandTableColumn(#"Expanded Name", "Date", {"Answer"}, {"Answer.1"}),
ExtractList1 = Table.TransformColumns(#"Expanded Date", {{"Product Purchased", each _[Answer]}}),
ExtractList2 = Table.TransformColumns(ExtractList1, {{"Serial Number", each _[Answer]}}),
#"Added Custom" = Table.AddColumn(ExtractList2, "NewTable", each Table.FromColumns({[Product Purchased], [Serial Number]})),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "WithIndex", each Table.AddIndexColumn([NewTable],"Index",1, 1)),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Product Purchased", "Serial Number", "NewTable"}),
#"Expanded WithIndex" = Table.ExpandTableColumn(#"Removed Columns", "WithIndex", {"Column1", "Column2", "Index"}, {"Column1", "Column2", "Index"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded WithIndex",{{"Answer", "Name"}, {"Answer.1", "Date"}, {"Column1", "Product Purchased"}, {"Column2", "Serial Number"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Serial Number", type text}, {"Product Purchased", type text}, {"Feedback ID", type text}, {"Index", type text}}),
#"Merged Columns" = Table.CombineColumns(#"Changed Type1",{"Feedback ID", "Index"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"RowID"),
#"Reordered Columns" = Table.ReorderColumns(#"Merged Columns",{"RowID", "Name", "Date", "Product Purchased", "Serial Number"})
in
#"Reordered Columns"
If this works for you, please mark it as the solution. Kudos are also appreciated. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
I really appreciate your prompt response @mahoneypat! This worked like a charm! The core here is to convert the data into a table then to a list. Actually the original data comes in JSON format. So I may try extracting table as lists directly from there. Awesome work!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.