Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all
I'm hoping someone can suggest some M code that can do the following.
For each unique value in the [Number] column, apply the following rules:
If all values in the [Value] column are "A" only, keep all rows
If all values in the [Value] column are "B" only, keep all rows
If the values in the [Value] column are a mix of "A" and "B", keep all rows of "A" only
I have tried to show how this would work in the table below, with the final column being the desired outcome.
Number | Value | Keep |
1 | B | Yes |
1 | B | Yes |
2 | A | Yes |
2 | B | No |
3 | A | Yes |
3 | A | Yes |
3 | B | No |
4 | A | Yes |
5 | B | Yes |
Thank you so much!
Solved! Go to Solution.
Try this solution in Power Query. It uses Group By and a custom column.
M code:
let
Source = Table.FromRows(
Json.Document(
Binary.Decompress(
Binary.FromText("i45WMlTSUXJSitVBZhkBWY5wFkTMGC6GyoLImsDFTCFisQA=", BinaryEncoding.Base64),
Compression.Deflate
)
),
let
_t = ((type nullable text) meta [Serialized.Text = true])
in
type table [Number = _t, Value = _t]
),
ChangeType = Table.TransformColumnTypes(Source, {{"Number", Int64.Type}}),
GroupRows = Table.Group(
ChangeType,
{"Number"},
{
{"Distinct Count", each Table.RowCount(Table.Distinct(_)), Int64.Type},
{"All", each _, type table [Number = nullable number, Value = nullable text]}
}
),
ExpandAll = Table.ExpandTableColumn(GroupRows, "All", {"Value"}, {"Value"}),
AddColumn = Table.AddColumn(
ExpandAll,
"Keep Column",
each
if [Distinct Count] = 1 then
"Y"
else if [Distinct Count] = 2 and [Value] = "A" then
"Y"
else
"N"
)
in
AddColumn
Result:
Proud to be a Super User!
Try this solution in Power Query. It uses Group By and a custom column.
M code:
let
Source = Table.FromRows(
Json.Document(
Binary.Decompress(
Binary.FromText("i45WMlTSUXJSitVBZhkBWY5wFkTMGC6GyoLImsDFTCFisQA=", BinaryEncoding.Base64),
Compression.Deflate
)
),
let
_t = ((type nullable text) meta [Serialized.Text = true])
in
type table [Number = _t, Value = _t]
),
ChangeType = Table.TransformColumnTypes(Source, {{"Number", Int64.Type}}),
GroupRows = Table.Group(
ChangeType,
{"Number"},
{
{"Distinct Count", each Table.RowCount(Table.Distinct(_)), Int64.Type},
{"All", each _, type table [Number = nullable number, Value = nullable text]}
}
),
ExpandAll = Table.ExpandTableColumn(GroupRows, "All", {"Value"}, {"Value"}),
AddColumn = Table.AddColumn(
ExpandAll,
"Keep Column",
each
if [Distinct Count] = 1 then
"Y"
else if [Distinct Count] = 2 and [Value] = "A" then
"Y"
else
"N"
)
in
AddColumn
Result:
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
99 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |