Forum Discussion
Power query
I would do it differently.
I did this:
- Seleccted the first 3 columns, then unpivoted other columns
- Filtered the "a" out of the Values column
- Grouped by the first 3 columns and did a CountRows.
I get this:
If you need the original data again, just merge it with the source step.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WqlDSUUoE4lQoDcGxOtFKVVVVQDaUhMoUgDFItqICpBNCwCXAkrEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, #"1/1/2021" = _t, #"1/2/2021" = _t, #"1/3/2021" = _t]), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Column1", "Column2", "Column3"}, "Attribute", "Value"), #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] = "p")), #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Column1", "Column2", "Column3"}, {{"Count", each Table.RowCount(_), Int64.Type}}), #"Merged Queries" = Table.NestedJoin(Source, {"Column1", "Column2", "Column3"}, #"Grouped Rows", {"Column1", "Column2", "Column3"}, "Grouped Rows", JoinKind.LeftOuter), #"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"Count"}, {"Count"}) in #"Expanded Grouped Rows"I would not recommend the final merge steps though. It is generally bad practice to leave dates in columns. They should be rows, both in Power Query and DAX.
But, it looks like this. This will work with any number of date columns.
How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.
Community ChampionTry this Anonymous - You can convert a record to a list, then search it. If it returns the text you are looking for, it will count the returns and return 0 if nothing was found, or 1 or more if it was found.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUUoC4mSlWJ1opRQgKxWI05RiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
#"Added Custom" =
Table.AddColumn(
Source,
"Custom",
each
List.Count(
List.FindText(
Record.ToList(Source{_}),
"a")
)
)
in
#"Added Custom"
I searched for the letter "a" here. I'd need more data to see what you are exactly trying to accomplish.
I have attached the screenshot above, In this, I have to calculate the number of P's in date columns for every row and these date columns is dynamic, it will change from list to list
- edhans
I would do it differently.
I did this:
- Seleccted the first 3 columns, then unpivoted other columns
- Filtered the "a" out of the Values column
- Grouped by the first 3 columns and did a CountRows.
I get this:
If you need the original data again, just merge it with the source step.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WqlDSUUoE4lQoDcGxOtFKVVVVQDaUhMoUgDFItqICpBNCwCXAkrEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, #"1/1/2021" = _t, #"1/2/2021" = _t, #"1/3/2021" = _t]), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Column1", "Column2", "Column3"}, "Attribute", "Value"), #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] = "p")), #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Column1", "Column2", "Column3"}, {{"Count", each Table.RowCount(_), Int64.Type}}), #"Merged Queries" = Table.NestedJoin(Source, {"Column1", "Column2", "Column3"}, #"Grouped Rows", {"Column1", "Column2", "Column3"}, "Grouped Rows", JoinKind.LeftOuter), #"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"Count"}, {"Count"}) in #"Expanded Grouped Rows"I would not recommend the final merge steps though. It is generally bad practice to leave dates in columns. They should be rows, both in Power Query and DAX.
But, it looks like this. This will work with any number of date columns.
How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.Thank you, It helped me to get the required output