cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
lukaszibula
New Member

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!

2 ACCEPTED SOLUTIONS
JW_van_Holst
Resolver III
Resolver III

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

View solution in original post

mahoneypat
Super User IV
Super User IV

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

5 REPLIES 5
mahoneypat
Super User IV
Super User IV

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

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"
JW_van_Holst
Resolver III
Resolver III

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

View solution in original post

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?

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors