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 have a table that looks like this:
Id | GroupId | Indicator | Boolean |
1 | A | Intl | Y |
2 | B | Dom | N |
3 | A | Intl | N |
4 | A | Intl | N |
5 | B | Dom | N |
6 | A | Intl | N |
7 | C | Intl | N |
8 | C | Intl | N |
What I need if a transformation that will change my Boolean as follows:
If a GroupId with a "Intl" Indicator has at least one row with a Boolean at Y, all the rows with the same GroupId get the Y.
My final table should look like this below, where Ids 3,4 and 6 had their Boolean changed to Y.
Id | GroupId | Indicator | Boolean |
1 | A | Intl | Y |
2 | B | Dom | N |
3 | A | Intl | Y |
4 | A | Intl | Y |
5 | B | Dom | N |
6 | A | Intl | Y |
7 | C | Intl | N |
8 | C | Intl | N |
Any help would be greatly appreciated.
Solved! Go to Solution.
As suggested by supergimi, i make steps below:
1. right-click on the query, make a duplicate to get "query 1",
2. in "Query 1", add a custom column, then remove other columns, only keep the "Custom" column, remove blank rows for this column
3.Merge queries, then expand this column
4. add a conditional column
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello
I would suggest you to use a combination of List.AnyTrue, Table.SelectRows and Table.TransformColumns
see my example here: I have decided to output a real logical instead Y or N. In case apply another TransformColumns
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIEYs+8khwgFakUqxOtZARkOQGxS34ukPQDixljU2iCTdAUi24zbArNgSxnhCBEpQWGYCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Id = _t, GroupId = _t, Indicator = _t, Boolean = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Id", Int64.Type}, {"GroupId", type text}, {"Indicator", type text}, {"Boolean", type text}}),
AddColumn = Table.AddColumn
(
#"Changed Type",
"New Boolean",
(newcolumn) =>
List.AnyTrue
(
Table.TransformColumns
(
Table.SelectRows
(
#"Changed Type",
each ([GroupId]= newcolumn[GroupId]) and ([Indicator]= "Intl")
),
{"Boolean", each if _ = "Y" then true else false}
)[Boolean]
)
)
in
AddColumn
Have fun
Jimmy
Below is a one query solution. Hope it helps,
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIEYs+8khwgFakUqxOtZARkOQGxS34ukPQDixmjKoQImmATNMWi2wybQnMgyxld0AJDMBYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Id = _t, GroupId = _t, Indicator = _t, Boolean = _t]),
#"Filtered Rows" = Table.SelectRows(Source, each ([Indicator] = "Intl") and ([Boolean] = "Y")),
#"Grouped Rows" = Table.Group(#"Filtered Rows", {"GroupId"}, {{"Count", each Table.RowCount(_), type number}}),
#"Merged Queries" = Table.NestedJoin(Source, {"GroupId"}, #"Grouped Rows", {"GroupId"}, "Grouped Rows", JoinKind.LeftOuter),
#"Added Custom" = Table.AddColumn(#"Merged Queries", "NewBoolean", each if Table.IsEmpty([Grouped Rows]) then [Boolean] else "Y"),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Boolean", "Grouped Rows"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"NewBoolean", "Boolean"}})
in
#"Renamed Columns"
As suggested by supergimi, i make steps below:
1. right-click on the query, make a duplicate to get "query 1",
2. in "Query 1", add a custom column, then remove other columns, only keep the "Custom" column, remove blank rows for this column
3.Merge queries, then expand this column
4. add a conditional column
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Create a calculated column
Column =
VAR flag =
CALCULATE (
COUNT ( 'Table'[GroupId] ),
FILTER (
'Table',
'Table'[GroupId] = EARLIER ( 'Table'[GroupId] )
&& 'Table'[Indicator] = "Intl"
&& 'Table'[Boolean] = "Y"
)
)
RETURN
IF ( flag = 1, "Y", [Boolean] )
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
calling your query A,
you can create two new queries, B and C referencing to A.
Then you can filter B set Indicator = "Intl" and Boolean = Y
Then select GroupId column in B and remove other columns
Remove duplicates from GroupId in B
Select C and merge query B by GroupId Column with left join condition
Expand the new B column in the C query
Add conditional column, named NewBoolean, setting this condition: if Indicator = "Intl" and B.GroupId is not null then Y else Boolean
Regards
Lorenzo
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.