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 got stuck with building a database in Power BI which shows who is responsible for what product scope.
Now I have two tables:
Table (i)
Group | Category | Basket |
Fruit | 1 | 12 |
Fruit | 1 | 13 |
Fruit | 2 | 14 |
Fruit | 3 | 15 |
Fruit | 4 | 16 |
Fruit | 4 | 17 |
Fruit | 4 | 18 |
Vegetable | 8 | 19 |
Vegetable | 8 | 20 |
Table (ii)
Responsible Name | Group | Category | Basket | Explanation |
A | Fruit | All | All | A manages all baskets under group Fruit |
B | Fruit | 4 | All | B manages all baskets under Category 4 under group Fruit |
C | Fruit | 1 | 12 | C manages basket 12 |
C | Fruit | 4 | 16 | C manages basket 16 |
D | Vegetable | 8 | All | D manages all baskets under Category 8 under group Vegetable |
I wonder if it possible to build some identifiers which can translate/break down the "All" in table (ii) by the structure in table (i), and eventually enable me to visualize in a table that looks like below, so users would not need to compare two tables at the same time to find out the specific baskets each is responsible for.
Responsible Name | Group | Category | Basket |
A | Fruit | 1 | 12 |
A | Fruit | 1 | 13 |
A | Fruit | 2 | 14 |
A | Fruit | 3 | 15 |
A | Fruit | 4 | 16 |
A | Fruit | 4 | 17 |
A | Fruit | 4 | 18 |
Your help would be much appreciated!!!
Solved! Go to Solution.
Hi, @gingersnapx
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
Table1:
Table2:
You may apply the following transformations in 'Advanced Editor' for 'Table2'.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXIrKs0sAdKOOTlQMlYnWskJScYESdwZSdwQhI0whEHKDc3Awi5AZlhqempJYlJOKpBtATMpFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Responsible Name" = _t, Group = _t, Category = _t, Basket = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Responsible Name", type text}, {"Group", type text}, {"Category", type text}, {"Basket", type text}}),
Custom1 = Table.TransformRows(#"Changed Type",
(x)=>
[Responsible Name=x[Responsible Name],Group=x[Group],Category= if x[Category]="All" then Table.SelectRows(Table1,(y)=>y[Group]=x[Group])[Category] else
List.Transform( Text.ToList(x[Category]),Number.From),Basket=x[Basket] ]
),
#"Converted to Table" = Table.FromList(Custom1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"Responsible Name", "Group", "Category", "Basket"}, {"Responsible Name", "Group", "Category", "Basket"}),
Custom2 = Table.TransformRows(#"Expanded Column1",
(x)=>[
Responsible Name = x[Responsible Name],
Group = x[Group],
Category = x[Category],
Basket =
if List.Count(x[Category])>1
then Table.SelectRows(Table1,(y)=>y[Group]=x[Group])[Basket]
else if x[Basket]="All"
then Table.SelectRows(Table1,(z)=>z[Group]=x[Group] and z[Category]=Number.From( List.First(x[Category])) )[Basket]
else {x[Basket]}
]
),
#"Converted to Table1" = Table.FromList(Custom2, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column2" = Table.ExpandRecordColumn(#"Converted to Table1", "Column1", {"Responsible Name", "Group", "Category", "Basket"}, {"Responsible Name", "Group", "Category", "Basket"}),
#"Added Custom" = Table.AddColumn(#"Expanded Column2", "Custom", each let
l=List.Zip({[Category],[Basket]}),
res=List.Transform(l,(x)=>Record.FromList({x{0},x{1}},{"Category","Basket"}))
in
res),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Category", "Basket"}),
#"Expanded Custom" = Table.ExpandListColumn(#"Removed Columns", "Custom"),
#"Expanded Custom1" = Table.ExpandRecordColumn(#"Expanded Custom", "Custom", {"Category", "Basket"}, {"Category", "Basket"}),
#"Filled Down" = Table.FillDown(#"Expanded Custom1",{"Category"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Filled Down",{{"Responsible Name", type text}, {"Group", type text}, {"Category", Int64.Type}, {"Basket", Int64.Type}})
in
#"Changed Type1"
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @gingersnapx
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
Table1:
Table2:
You may apply the following transformations in 'Advanced Editor' for 'Table2'.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXIrKs0sAdKOOTlQMlYnWskJScYESdwZSdwQhI0whEHKDc3Awi5AZlhqempJYlJOKpBtATMpFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Responsible Name" = _t, Group = _t, Category = _t, Basket = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Responsible Name", type text}, {"Group", type text}, {"Category", type text}, {"Basket", type text}}),
Custom1 = Table.TransformRows(#"Changed Type",
(x)=>
[Responsible Name=x[Responsible Name],Group=x[Group],Category= if x[Category]="All" then Table.SelectRows(Table1,(y)=>y[Group]=x[Group])[Category] else
List.Transform( Text.ToList(x[Category]),Number.From),Basket=x[Basket] ]
),
#"Converted to Table" = Table.FromList(Custom1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"Responsible Name", "Group", "Category", "Basket"}, {"Responsible Name", "Group", "Category", "Basket"}),
Custom2 = Table.TransformRows(#"Expanded Column1",
(x)=>[
Responsible Name = x[Responsible Name],
Group = x[Group],
Category = x[Category],
Basket =
if List.Count(x[Category])>1
then Table.SelectRows(Table1,(y)=>y[Group]=x[Group])[Basket]
else if x[Basket]="All"
then Table.SelectRows(Table1,(z)=>z[Group]=x[Group] and z[Category]=Number.From( List.First(x[Category])) )[Basket]
else {x[Basket]}
]
),
#"Converted to Table1" = Table.FromList(Custom2, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column2" = Table.ExpandRecordColumn(#"Converted to Table1", "Column1", {"Responsible Name", "Group", "Category", "Basket"}, {"Responsible Name", "Group", "Category", "Basket"}),
#"Added Custom" = Table.AddColumn(#"Expanded Column2", "Custom", each let
l=List.Zip({[Category],[Basket]}),
res=List.Transform(l,(x)=>Record.FromList({x{0},x{1}},{"Category","Basket"}))
in
res),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Category", "Basket"}),
#"Expanded Custom" = Table.ExpandListColumn(#"Removed Columns", "Custom"),
#"Expanded Custom1" = Table.ExpandRecordColumn(#"Expanded Custom", "Custom", {"Category", "Basket"}, {"Category", "Basket"}),
#"Filled Down" = Table.FillDown(#"Expanded Custom1",{"Category"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Filled Down",{{"Responsible Name", type text}, {"Group", type text}, {"Category", Int64.Type}, {"Basket", Int64.Type}})
in
#"Changed Type1"
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
This is amazing!! Thanks Allan!
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |