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'd like to create a column that returns a True / False based on the minimum value in a column per each type. I'm not the best at explaining - please take a look at the example:
Thanks for taking a look!
Solved! Go to Solution.
Here is a step by step way to do it in Power Query:
1) Group by the columns to get the max value, somethin like below:
2) Apply a left outer join to merge the main table and the grouped by table:
= Table.NestedJoin(#"Changed Type", {"Value","Type"}, #"Grouped Rows", {"New","Type"}, "Grouped Rows", JoinKind.LeftOuter)
3) Now expand the columns to get final Result column:
= Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"New"}, {"Result"})
4) Now replace the values as per your requirement
= Table.ReplaceValue(#"Expanded Grouped Rows", each [Result],each if [Result] = null then "False" else "True",Replacer.ReplaceValue,{"Result"})
Complete M Code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCs5PzlbSUTJUitUBcjIyi0qAPGMUnikKzxLKy08tRlIJ4QFNiQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Type = _t, Value = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Type", type text}, {"Value", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Type"}, {{"New", each List.Min([Value]), type nullable number}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Value","Type"}, #"Grouped Rows", {"New","Type"}, "Grouped Rows", JoinKind.LeftOuter),
#"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"New"}, {"Result"}),
#"Replaced Value" = Table.ReplaceValue(#"Expanded Grouped Rows", each [Result],each if [Result] = null then "False" else "True",Replacer.ReplaceValue,{"Result"})
in
#"Replaced Value"
Here is a step by step way to do it in Power Query:
1) Group by the columns to get the max value, somethin like below:
2) Apply a left outer join to merge the main table and the grouped by table:
= Table.NestedJoin(#"Changed Type", {"Value","Type"}, #"Grouped Rows", {"New","Type"}, "Grouped Rows", JoinKind.LeftOuter)
3) Now expand the columns to get final Result column:
= Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"New"}, {"Result"})
4) Now replace the values as per your requirement
= Table.ReplaceValue(#"Expanded Grouped Rows", each [Result],each if [Result] = null then "False" else "True",Replacer.ReplaceValue,{"Result"})
Complete M Code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCs5PzlbSUTJUitUBcjIyi0qAPGMUnikKzxLKy08tRlIJ4QFNiQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Type = _t, Value = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Type", type text}, {"Value", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Type"}, {{"New", each List.Min([Value]), type nullable number}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Value","Type"}, #"Grouped Rows", {"New","Type"}, "Grouped Rows", JoinKind.LeftOuter),
#"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"New"}, {"Result"}),
#"Replaced Value" = Table.ReplaceValue(#"Expanded Grouped Rows", each [Result],each if [Result] = null then "False" else "True",Replacer.ReplaceValue,{"Result"})
in
#"Replaced Value"
Hi @peterso
Paste it to Advanced Editor:
let
Source = Table.FromRows(
Json.Document(
Binary.Decompress(
Binary.FromText(
"i45WCs5PzlbSUTJUitUBcjIyi0qAPGMUnikKzxLKy08tRlIJ4QFNiQUA",
BinaryEncoding.Base64
),
Compression.Deflate
)
),
let
_t = ((type nullable text) meta [Serialized.Text = true])
in
type table [Type = _t, Value = _t]
),
#"Changed Type" = Table.TransformColumnTypes(Source, {{"Type", type text}, {"Value", Int64.Type}}),
MinValue = Table.Group(
#"Changed Type",
{"Type"},
{{"Min", each List.Min([Value]), type nullable number}}
),
#"Merged Queries" = Table.NestedJoin(
#"Changed Type",
{"Type"},
MinValue,
{"Type"},
"Changed Type",
JoinKind.LeftOuter
),
#"Expanded Changed Type" = Table.ExpandTableColumn(
#"Merged Queries",
"Changed Type",
{"Min"},
{"Min"}
),
#"Added Custom" = Table.AddColumn(#"Expanded Changed Type", "Result", each [Value] = [Min]),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom", {"Min"})
in
#"Removed Columns"
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.