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.
Hello,
I have a table of data such as the one below, I am looking to add on the final two columns if possible.
if the individual has Rank = 1 at all then apply a 1 to every row associated with that individual
if the individual has Rank = 1 on that particular account, then apply a 1 to every row that matches both the individual and account criteria
Individual | Account | Rank | Does the Individual have Rank = 1 | Does the Individual have Rank = 1 on that Account |
Joe Bloggs | ABC | 1 | 1 | 1 |
Joe Bloggs | ABC | 0 | 1 | 1 |
Joe Bloggs | 123 | 0 | 1 | 0 |
Joe Bloggs | XYZ | 1 | 1 | 1 |
Joe Bloggs | XYZ | 0 | 1 | 1 |
Steve Strong | ABC | 0 | 0 | 0 |
Steve Strong | ABC | 0 | 0 | 0 |
Steve Strong | ABC | 0 | 0 | 0 |
I'm not sure if this is possible but would welcome feedback,
I am exploring trying to solve this using group by, and then fill down
Thanks
Tom
Solved! Go to Solution.
Hello @tomshaw83
here some more compact approach if needed
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8spPVXDKyU9PL1bSUXJ0cgaShkqxOlglDDAlDI2MsUtEREZhNwoiAdERXJJalqoQXFKUn5eOZgsZUrEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Individual = _t, Account = _t, Rank = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Individual", type text}, {"Account", type text}, {"Rank", Int64.Type}}),
HasIndividualAccountRank1 = Table.Group(#"Changed Type", {"Individual", "Account"}, {{"HasRank1", each if List.Contains(_[Rank], 1) then 1 else 0, type number}}),
HasIndividualRank1 = Table.Group(#"Changed Type", {"Individual"}, {{"HasRank1", each if List.Contains(_[Rank], 1) then 1 else 0, type number}}),
JoinIndividualRank1 = Table.NestedJoin
(
#"Changed Type",
{"Individual"},
HasIndividualRank1,
{"Individual"},
"HasIndividualRank1"
),
JoinIndividualAccountRank1 = Table.NestedJoin
(
JoinIndividualRank1,
{"Individual", "Account"},
HasIndividualAccountRank1,
{"Individual", "Account"},
"JoinIndividualAccountRank1"
),
#"Expanded HasIndividualRank1" = Table.ExpandTableColumn(JoinIndividualAccountRank1, "HasIndividualRank1", {"HasRank1"}, {"Does the Individual have Rank = 1"}),
#"Expanded JoinIndividualAccountRank1" = Table.ExpandTableColumn(#"Expanded HasIndividualRank1", "JoinIndividualAccountRank1", {"HasRank1"}, {"Does the Individual have Rank = 1 on that Account"})
in
#"Expanded JoinIndividualAccountRank1"
Copy paste this code to the advanced editor in a new blank query to see how the solution works.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
Hello @tomshaw83
here some more compact approach if needed
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8spPVXDKyU9PL1bSUXJ0cgaShkqxOlglDDAlDI2MsUtEREZhNwoiAdERXJJalqoQXFKUn5eOZgsZUrEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Individual = _t, Account = _t, Rank = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Individual", type text}, {"Account", type text}, {"Rank", Int64.Type}}),
HasIndividualAccountRank1 = Table.Group(#"Changed Type", {"Individual", "Account"}, {{"HasRank1", each if List.Contains(_[Rank], 1) then 1 else 0, type number}}),
HasIndividualRank1 = Table.Group(#"Changed Type", {"Individual"}, {{"HasRank1", each if List.Contains(_[Rank], 1) then 1 else 0, type number}}),
JoinIndividualRank1 = Table.NestedJoin
(
#"Changed Type",
{"Individual"},
HasIndividualRank1,
{"Individual"},
"HasIndividualRank1"
),
JoinIndividualAccountRank1 = Table.NestedJoin
(
JoinIndividualRank1,
{"Individual", "Account"},
HasIndividualAccountRank1,
{"Individual", "Account"},
"JoinIndividualAccountRank1"
),
#"Expanded HasIndividualRank1" = Table.ExpandTableColumn(JoinIndividualAccountRank1, "HasIndividualRank1", {"HasRank1"}, {"Does the Individual have Rank = 1"}),
#"Expanded JoinIndividualAccountRank1" = Table.ExpandTableColumn(#"Expanded HasIndividualRank1", "JoinIndividualAccountRank1", {"HasRank1"}, {"Does the Individual have Rank = 1 on that Account"})
in
#"Expanded JoinIndividualAccountRank1"
Copy paste this code to the advanced editor in a new blank query to see how the solution works.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
So, @tomshaw83 , if I understand what you are asking try this. I did this in power query using group by. Here is the final result, and the code that you can paste into Advanced Editor. I used group by twice, once for each column.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8spPVXDKyU9PL1bSUXJ0cgaShnAcq4NVgQFuBYZGxkgKDDAVRERG4bcCogDViuCS1LJUheCSovy8dBRXGMAtoYKSWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Individual = _t, Account = _t, #"Rank " = _t, #"Does the Individual have Rank = 1" = _t, #"Does the Individual have Rank = 1 on that Account" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Individual", type text}, {"Account", type text}, {"Rank ", Int64.Type}, {"Does the Individual have Rank = 1", Int64.Type}, {"Does the Individual have Rank = 1 on that Account", Int64.Type}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Does the Individual have Rank = 1", "Does the Individual have Rank = 1 on that Account"}),
#"Grouped Rows" = Table.Group(#"Removed Columns", {"Individual"}, {{"Sum for Individual", each List.Sum([#"Rank "]), type nullable number}, {"Detail", each _, type table [Individual=nullable text, Account=nullable text, #"Rank "=nullable number]}}),
#"Expanded Detail" = Table.ExpandTableColumn(#"Grouped Rows", "Detail", {"Account", "Rank "}, {"Detail.Account", "Detail.Rank "}),
#"Added Conditional Column" = Table.AddColumn(#"Expanded Detail", "Does the Individual have Rank = 1", each if [Sum for Individual] > 0 then 1 else 0),
#"Removed Columns1" = Table.RemoveColumns(#"Added Conditional Column",{"Sum for Individual"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns1",{{"Does the Individual have Rank = 1", Int64.Type}, {"Detail.Rank ", Int64.Type}}),
#"Grouped Rows1" = Table.Group(#"Changed Type1", {"Individual", "Detail.Account"}, {{"sum double", each List.Sum([#"Detail.Rank "]), type nullable number}, {"detail2", each _, type table [Individual=nullable text, Detail.Account=nullable text, #"Detail.Rank "=nullable number, #"Does the Individual have Rank = 1"=nullable number]}}),
#"Expanded detail2" = Table.ExpandTableColumn(#"Grouped Rows1", "detail2", {"Detail.Rank ", "Does the Individual have Rank = 1"}, {"Detail.Rank ", "Does the Individual have Rank = 1"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded detail2",{{"sum double", "Does the Individual have Rank = 1 on that Account"}}),
#"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Individual", "Detail.Account", "Detail.Rank ", "Does the Individual have Rank = 1 on that Account", "Does the Individual have Rank = 1"}),
#"Renamed Columns1" = Table.RenameColumns(#"Reordered Columns",{{"Detail.Account", "Account"}, {"Detail.Rank ", "Rank"}}),
#"Reordered Columns1" = Table.ReorderColumns(#"Renamed Columns1",{"Individual", "Account", "Rank", "Does the Individual have Rank = 1", "Does the Individual have Rank = 1 on that Account"})
in
#"Reordered Columns1"
My table is named Table, and I inserted a copy of what you gave us to start the query.
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel
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.