How to build a database mapping Responsible Names and their specific job scopes in Power BI

[gingersnapx]

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) shows the structure of product scope (basket < category < group)
• Table (ii) shows who is responsible for what

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!!!

[v-alq-msft · Accepted 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.

[gingersnapx]

This is amazing!! Thanks Allan!