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

Question

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 &lt; category &lt; group)Table (ii) shows who is responsible for whatTable (i)Group&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;CategoryBasket&nbsp;Fruit112Fruit113Fruit214Fruit315Fruit416Fruit417Fruit418Vegetable819Vegetable820&nbsp;Table (ii)Responsible NameGroupCategory&nbsp;&nbsp;Basket&nbsp;&nbsp;ExplanationAFruitAllAllA manages all baskets under group FruitBFruit4AllB manages all baskets under Category 4 under group FruitCFruit112C manages basket 12CFruit416C manages basket 16DVegetable8AllD manages all baskets under Category 8 under group Vegetable&nbsp;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&nbsp;Group&nbsp;Category&nbsp;Basket&nbsp;AFruit112AFruit113AFruit214AFruit315AFruit416AFruit417AFruit418Your help would be much appreciated!!!

v-alq-msft · Accepted Answer

Hi,&nbsp;gingersnapx&nbsp;
&nbsp;
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
Table1:

Table2:

&nbsp;
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)=&gt;
[Responsible Name=x[Responsible Name],Group=x[Group],Category= if x[Category]="All" then Table.SelectRows(Table1,(y)=&gt;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)=&gt;[
     Responsible Name = x[Responsible Name],
     Group = x[Group],
     Category = x[Category],
     Basket = 
     if List.Count(x[Category])&gt;1
     then Table.SelectRows(Table1,(y)=&gt;y[Group]=x[Group])[Basket]
     else if x[Basket]="All"
          then Table.SelectRows(Table1,(z)=&gt;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)=&gt;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"
&nbsp;
Result:

&nbsp;
Best Regards
Allan
&nbsp;
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Forum Discussion

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

2 Replies

Related content

Assign my database artifact to specific users or share it?

Database connectivity.

Dashboard responsiveness

Mobile responsiveness

Fabric/Copilot late response

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

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

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