Forum Discussion
Counter for one unique expression for every row
https://drive.google.com/file/d/1cBpewQpejSBMtK2Pvk-_-t9pH0ORh3Ue/view?usp=sharing
see the Pbix in the link. Outline of the solution:
- add an index column to the table;
- duplicate the table (table (2);
- filter table (2) on "Date";
- add an index on tabel(2) (starting at 1);
- merge table and table (2) on index;
- fill the index column down;
Good luck,
//JW
Here is one way to do it in the query editor. To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below. Note that the Rename and Trim steps were only needed as the data you pasted had leading/trailing spaces.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckksST20ADtU0lFCF1IwMjAy1DMw1jMwUYrViVZyzs8tSMyrxKNXISS1uCQZogysxTOvuKSoNDc1r0SjWBNdMULSkBTFRjDHgDjFONwCdolCMkQRWAPZnjcjw/NGNPY9kYqNiQqq/JKM1CJEWMUCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"voteOptionText " = _t, #" voteAnswer" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"voteOptionText ", type text}, {" voteAnswer", type text}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"voteOptionText ", "voteOptionText"}, {" voteAnswer", "voteAnswer"}}), #"Trimmed Text" = Table.TransformColumns(#"Renamed Columns",{{"voteOptionText", Text.Trim, type text}, {"voteAnswer", Text.Trim, type text}}), #"Added Custom" = Table.AddColumn(#"Trimmed Text", "DateForFillDown", each if [voteOptionText] = "Date" then [voteAnswer] else null, type text), #"Filled Down" = Table.FillDown(#"Added Custom",{"DateForFillDown"}), #"Grouped Rows" = Table.Group(#"Filled Down", {"DateForFillDown"}, {{"AllRows", each _, type table [voteOptionText=text, voteAnswer=text, DateForFillDown=text]}}), #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 1, 1, Int64.Type), #"Expanded AllRows" = Table.ExpandTableColumn(#"Added Index", "AllRows", {"voteOptionText", "voteAnswer"}, {"voteOptionText", "voteAnswer"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded AllRows",{"DateForFillDown"}) in #"Removed Columns"Pat
Absolutely awesome! Thanks! I didn't know about the Tabe.Filldown function, I guess that's also what JW_van_Holst meant. Works perfectly!
To be sure. Here is the solution in a single query.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckksSVXSUTIyMDLUMzDWMzBRitWJVnLOzy1IzKsESoSkFpckQ3kgGc+84pKi0tzUvBKNYk2gPIJvSEDeCGYyiAM1WSEZygVJoTvFDLdTjKjiFtzyxmhuzS/JSC1CODYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [voteOptionText = _t, voteAnswer = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"voteOptionText", type text}, {"voteAnswer", type text}}),
AddedIndex = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
#"Filtered Rows" = Table.SelectRows(AddedIndex, each ([voteOptionText] = "Date")),
DateIndex = Table.AddIndexColumn(#"Filtered Rows", "Index.1", 1, 1, Int64.Type),
Custom1 = Table.NestedJoin(AddedIndex, {"Index"}, DateIndex, {"Index"}, "Join", JoinKind.LeftOuter),
#"Expanded Join" = Table.ExpandTableColumn(Custom1, "Join", {"Index.1"}, {"Index.1"}),
#"Filled Down" = Table.FillDown(#"Expanded Join",{"Index.1"}),
#"Renamed Columns" = Table.RenameColumns(#"Filled Down",{{"Index.1", "respondent"}}),
#"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Index"})
in
#"Removed Columns"