Counter for one unique expression for every row

[lukaszibula]

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!

[JW_van_Holst · Accepted solution]

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

[mahoneypat · Accepted solution]

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

[lukaszibula]

Absolutely awesome! Thanks! I didn't know about the Tabe.Filldown function, I guess that's also what JW_van_Holst meant. Works perfectly!

[JW_van_Holst]

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"

[JW_van_Holst · Accepted solution]

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

[lukaszibula]

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?