Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
I have a list that has a column containing comma seperated numbers. I have worked out a way (well copied a way from someone else) to count how many rows have the same value AND part ID as the current one. However, what I want to do is, instead of searching for the entire comma seperated text value, is to search for the first number within that value. So essentially split it, and then do a search for that first split value WITHIN the same column.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkgsKjE0NlfSUTI0MdIxNjZRitWBiVoARY0MTXTMjE11jM0NdMwNzJFkLbHqGTVp1CS4SdhNwdQ9NHxDM5NAomhaaWY7fnvQLBj0ivGHAmG9aFyQMWAjYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [PARTID = _t, #"Matching Response Path" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"PARTID", type text}, {"Matching Response Path", type text}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"PARTID", Order.Ascending}}),
Custom1 = Table.AddColumn(#"Sorted Rows", "Count", each Table.RowCount(Table.SelectRows(#"Sorted Rows",(R) => _[Matching Response Path] = R[Matching Response Path] and _[PARTID] = R[PARTID])), type number)
in
Custom1
Solved! Go to Solution.
You can use a custom column with this expression to get your desired result.
= let
thispart = [PARTID],
thisfirst = Text.Split([Matching Response Path], ","){0},
firstlist = List.Transform(Table.SelectRows(#"Sorted Rows", each [PARTID] = thispart)[Matching Response Path], each Text.Split(_, ","){0}),
result = List.Count(List.Select(firstlist, each _ = thisfirst))
in
result
And thank you for pasting your data as M code! Makes it much easier to help. Here is the full query back.
let
Source = Table.FromRows(
Json.Document(
Binary.Decompress(
Binary.FromText(
"i45WCkgsKjE0NlfSUTI0MdIxNjZRitWBiVoARY0MTXTMjE11jM0NdMwNzJFkLbHqGTVp1CS4SdhNwdQ9NHxDM5NAomhaaWY7fnvQLBj0ivGHAmG9aFyQMWAjYgE=",
BinaryEncoding.Base64
),
Compression.Deflate
)
),
let
_t = ((type nullable text) meta [Serialized.Text = true])
in
type table [PARTID = _t, #"Matching Response Path" = _t]
),
#"Changed Type" = Table.TransformColumnTypes(
Source,
{{"PARTID", type text}, {"Matching Response Path", type text}}
),
#"Sorted Rows" = Table.Sort(#"Changed Type", {{"PARTID", Order.Ascending}}),
Custom1 = Table.AddColumn(
#"Sorted Rows",
"Count",
each Table.RowCount(
Table.SelectRows(
#"Sorted Rows",
(R) => _[Matching Response Path] = R[Matching Response Path] and _[PARTID] = R[PARTID]
)
),
type number
),
#"Added Custom" = Table.AddColumn(
Custom1,
"Custom",
each
let
thispart = [PARTID],
thisfirst = Text.Split([Matching Response Path], ","){0},
firstlist = List.Transform(
Table.SelectRows(#"Sorted Rows", each [PARTID] = thispart)[Matching Response Path],
each Text.Split(_, ","){0}
),
result = List.Count(List.Select(firstlist, each _ = thisfirst))
in
result
)
in
#"Added Custom"
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@mahoneypat has a good solution but you can also do this with almost no need to write M yourself by using Group By and Merge Queries.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkgsKjE0NlfSUTI0MdIxNjZRitWBiVoARY0MTXTMjE11jM0NdMwNzJFkLbHqGTVp1CS4SdhNwdQ9NHxDM5NAomhaaWY7fnvQLBj0ivGHAmG9aFyQMWAjYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [PARTID = _t, #"Matching Response Path" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"PARTID", type text}, {"Matching Response Path", type text}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"PARTID", Order.Ascending}}),
#"Inserted Text Before Delimiter" = Table.AddColumn(#"Sorted Rows", "Text Before Delimiter", each Text.BeforeDelimiter([Matching Response Path], ","), type text),
#"Grouped Rows" = Table.Group(#"Inserted Text Before Delimiter", {"PARTID", "Text Before Delimiter"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
#"Merged Queries" = Table.NestedJoin(#"Inserted Text Before Delimiter", {"PARTID", "Text Before Delimiter"}, #"Grouped Rows", {"PARTID", "Text Before Delimiter"}, "Grouped Rows", JoinKind.LeftOuter),
#"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"Count"}, {"Count"})
in
#"Expanded Grouped Rows"
I think this has better computational efficiency too.
You can use a custom column with this expression to get your desired result.
= let
thispart = [PARTID],
thisfirst = Text.Split([Matching Response Path], ","){0},
firstlist = List.Transform(Table.SelectRows(#"Sorted Rows", each [PARTID] = thispart)[Matching Response Path], each Text.Split(_, ","){0}),
result = List.Count(List.Select(firstlist, each _ = thisfirst))
in
result
And thank you for pasting your data as M code! Makes it much easier to help. Here is the full query back.
let
Source = Table.FromRows(
Json.Document(
Binary.Decompress(
Binary.FromText(
"i45WCkgsKjE0NlfSUTI0MdIxNjZRitWBiVoARY0MTXTMjE11jM0NdMwNzJFkLbHqGTVp1CS4SdhNwdQ9NHxDM5NAomhaaWY7fnvQLBj0ivGHAmG9aFyQMWAjYgE=",
BinaryEncoding.Base64
),
Compression.Deflate
)
),
let
_t = ((type nullable text) meta [Serialized.Text = true])
in
type table [PARTID = _t, #"Matching Response Path" = _t]
),
#"Changed Type" = Table.TransformColumnTypes(
Source,
{{"PARTID", type text}, {"Matching Response Path", type text}}
),
#"Sorted Rows" = Table.Sort(#"Changed Type", {{"PARTID", Order.Ascending}}),
Custom1 = Table.AddColumn(
#"Sorted Rows",
"Count",
each Table.RowCount(
Table.SelectRows(
#"Sorted Rows",
(R) => _[Matching Response Path] = R[Matching Response Path] and _[PARTID] = R[PARTID]
)
),
type number
),
#"Added Custom" = Table.AddColumn(
Custom1,
"Custom",
each
let
thispart = [PARTID],
thisfirst = Text.Split([Matching Response Path], ","){0},
firstlist = List.Transform(
Table.SelectRows(#"Sorted Rows", each [PARTID] = thispart)[Matching Response Path],
each Text.Split(_, ","){0}
),
result = List.Count(List.Select(firstlist, each _ = thisfirst))
in
result
)
in
#"Added Custom"
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Works perfectly, many thanks Pat!
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
68 | |
61 | |
19 | |
17 | |
13 |