Forum Discussion
Count text value appearances across whole table
Hi Anonymous
You can best unpivot the finisher columns first (in PQ), then build a very simple measure. You can also use a slicer to choose if you want to see only fist finishers, 1st and 2nd , all finishers, etc.
See it all at work in the attached file.
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Community ChampionHi Anonymous
You can best unpivot the finisher columns first (in PQ), then build a very simple measure. You can also use a slicer to choose if you want to see only fist finishers, 1st and 2nd , all finishers, etc.
See it all at work in the attached file.
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Hi AIB,
Unpivoting in M was my first choise also, but you were one step ahead :). Congrats!
My solution is a little bit different, as I choose to use grouping instead measure for counting. What do you think?
let
Source = Csv.Document(File.Contents("C:\xxxxxxxxxxx\exp BI forum.txt"),[Delimiter=" ", Columns=7, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Race", type text}, {"Date", type date}, {"Country", type text}, {"First Finisher", type text}, {"Second Finisher", type text}, {"Third Finisher", type text}, {"Fouth Finisher", type text}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Race", "Date", "Country"}, "Attribute", "Value"),
#"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"Value"}, {{"top finishers", each Table.RowCount(_), Int64.Type}}),
#"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"top finishers", Order.Descending}})
in
#"Sorted Rows"