Forum Discussion
Counter for one unique expression for every row
Hello,
I try to get a counter for an unique expression in my table. My table looks something like this:
voteOptionText voteAnswer
Date 2021.03.04
Company Testcompany
Instrument(s) Instrument1
Instrument(s) Instrument2
Comments Test comment
Date 2021.03.06
Company Testcompany2
Instrument(s) Instrument1
Instrument(s) Instrument2
Instrument(s) Instrument3
Comments other comment
so everytime the expression "Date" apears in the voteOptionText column the counter should increas by one, so that I'm able to distinguish between the diffrent persons who gave the awnsers, because I know every new set of data begins with the date expression. So it should look like this:
voteOptionText voteAnswer #
Date 2021.03.04 1
Company Testcompany 1
Instrument(s) Instrument1 1
Instrument(s) Instrument2 1
Comments Test comment 1
Date 2021.03.06 2
Company Testcompany2 2
Instrument(s) Instrument1 2
Instrument(s) Instrument2 2
Instrument(s) Instrument3 2
Comments other comment 2
So the counter should only count the word "date" and not other expressions.
I need this counter to pivot the table afterwards and to distinguish multiple answers. So the next step would be to pivot the index column. Do you have any idea how to get this counter? I appreciate any help!
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
5 Replies
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
Thank you very much for your help!
Unfortunately I'm not able to open your file, but I tried to do the steps you mentioned manually. But if I do that I get a table with numbers on each row with the expression "data". The rest of the rows gets filled with "null". It looks like that:
voteOptionText voteAnswer index index.1.index index.1.index.1
Date 2021.03.04 1 1 1
Company Testcompany 2 null null
Instrument(s) Instrument1 3 null null
Instrument(s) Instrument2 4 null null
Comments Test comment 5 null null
Date 2021.03.06 6 34 2
Company Testcompany2 7 null null
Instrument(s) Instrument1 8 null null
Instrument(s) Instrument2 9 null null
Instrument(s) Instrument3 10 null null
Comments other comment 11 null null
What did I wrong?
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"
