Rank duplicate entries with unique values in Power Query

[Anonymous]

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'),

FILTER('Table1', [ColIndex] <=EARLIER([ColIndex])),

FILTER('Table1', [Feedback ID]=EARLIER([Feedback ID])),

FILTER('Table1', [question]=EARLIER([question]))

)

3. Created Table2 and concatanated Feedback ID and Multiple Entry Index columns (Row ID) to generate a unique marker for each row I'd like to produce. Here's how it looks:

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_____________________________________________________

[mahoneypat · Accepted 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

[Anonymous]

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!