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
Microsoft
Microsoft

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
Microsoft
Microsoft

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


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

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
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 24 without aka link and time 768x460.jpg

Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.