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.
I am required to add the value of column C based on the value of (column A & column B). The value has to be inserted based on below conditions -
First step -> Get VAR x = value of column[B] where Column [A] = "Mango"
Second step -> Get the value VAR y = value from column[C] where VALUE (column [B]) = x and column[A] ="Apple"
Third step -> insert y in column[C] where Column [A] = "Mango" and (column [B]) = x
Example in the below table 1st row and Column C should have 1993
2nd row column C should have 5555
3rd row column C should have 66666
Solved! Go to Solution.
Hi @tanny1234
Here I have two workarounds to achieve your goal.
1. If your table looks like the screenshot, where the A columns with the same B values are arranged in the order of Mango and Apple, then you can try the Fill Up/Down function in the Power Query Editor.
My Sample is the same like yours.
Result is as below.
2. Try Group By in Power Query Editor.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZA7DoAgDEDv0tlBfkJHD+AJiIODcTHK/SdbIJqCr0OTl5cOjRGW7TpuGECFEWnBOkSYUzr3zylEk/3bYp9m5QhZhvBzNbuJkbH3vouL84yMiS4uLmRErYm2rg4ZERuijauzjkfUlmjr6pQ2lh+yPg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t, B = _t, C = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"A", type text}, {"B", Int64.Type}, {"C", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"B"}, {{"Rows", each _, type table [A=nullable text, B=nullable number, C=nullable number]}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Grouped Rows", "Rows", "Rows - Copy"),
#"Expanded Rows - Copy" = Table.ExpandTableColumn(#"Duplicated Column", "Rows - Copy", {"C"}, {"Rows - Copy.C"}),
#"Grouped Rows1" = Table.Group(#"Expanded Rows - Copy", {"B", "Rows"}, {{"Max", each List.Max([#"Rows - Copy.C"]), type nullable number}}),
#"Expanded Rows" = Table.ExpandTableColumn(#"Grouped Rows1", "Rows", {"A"}, {"Rows.A"}),
#"Reordered Columns" = Table.ReorderColumns(#"Expanded Rows",{"Rows.A", "B", "Max"}),
#"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Rows.A", "A"}, {"Max", "C"}})
in
#"Renamed Columns"
For reference: Grouping or summarizing rows
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@amitchandakThanks for looking into it. Column B will have unique values for each entry of Column A, for e.g. "1809" will only exist 2 times, 1 each for Mango and Apple. Does it help?
Hi @tanny1234
Here I have two workarounds to achieve your goal.
1. If your table looks like the screenshot, where the A columns with the same B values are arranged in the order of Mango and Apple, then you can try the Fill Up/Down function in the Power Query Editor.
My Sample is the same like yours.
Result is as below.
2. Try Group By in Power Query Editor.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZA7DoAgDEDv0tlBfkJHD+AJiIODcTHK/SdbIJqCr0OTl5cOjRGW7TpuGECFEWnBOkSYUzr3zylEk/3bYp9m5QhZhvBzNbuJkbH3vouL84yMiS4uLmRErYm2rg4ZERuijauzjkfUlmjr6pQ2lh+yPg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t, B = _t, C = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"A", type text}, {"B", Int64.Type}, {"C", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"B"}, {{"Rows", each _, type table [A=nullable text, B=nullable number, C=nullable number]}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Grouped Rows", "Rows", "Rows - Copy"),
#"Expanded Rows - Copy" = Table.ExpandTableColumn(#"Duplicated Column", "Rows - Copy", {"C"}, {"Rows - Copy.C"}),
#"Grouped Rows1" = Table.Group(#"Expanded Rows - Copy", {"B", "Rows"}, {{"Max", each List.Max([#"Rows - Copy.C"]), type nullable number}}),
#"Expanded Rows" = Table.ExpandTableColumn(#"Grouped Rows1", "Rows", {"A"}, {"Rows.A"}),
#"Reordered Columns" = Table.ReorderColumns(#"Expanded Rows",{"Rows.A", "B", "Max"}),
#"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Rows.A", "A"}, {"Max", "C"}})
in
#"Renamed Columns"
For reference: Grouping or summarizing rows
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@tanny1234 , I doubt this can work unless there is one more reference
First value Column [A] = "Mango" B can have any value from Min to Max, what to take?
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 |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |