cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Abbi
Frequent Visitor

Need hep on Matrix table visualization

Hello All,

 

I have Billdetails table and DependentBill details table and Detailed dependent table respectively.

BillNumberStateCreation dateDescription
101success12-AugGroceries 
102success14-Augfurniture
103success02-SepBakery
104fail16-SepDairy
105processing23-OctBakery

 

 

DependentBillNmberStateCreation dateDescription
2001success13-Aug 
2002success20-Aug 
2003success04-Sep 
2004fail05-Sep 
2005fail26-Oct 
2006processing20-Sep 
2007fail24-Oct 
2008success25-Oct 

 

BillNumber Dependent Bill number 
1012001
1022001
1032003
1042005
105 
1032006
1042008
1022002

 

Looking to create a visual using matrix table as shown below: Required output

BillNumberDependent Bill number statecreation date 
101 success12-Aug 
2001success13-Aug 
102 success14-Aug 
2001success13-Aug 
2002success20-Aug 
103 success02-Sep 
2003success04-Sep 
2006processing20-Sep 

 

Please help

1 ACCEPTED SOLUTION

@Abbi 

See if this works for you:

result.PNG

 I created the table in Power Query using the following code:

let
    Source = Table.NestedJoin(BillNumber, {"BillNumber"}, Details, {"BillNumber "}, "Details", JoinKind.LeftOuter),
    #"Expanded Details" = Table.ExpandTableColumn(Source, "Details", {"Dependent Bill number "}, {"Details.Dependent Bill number "}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Details",{"Creation date", "Description", "State"}),
    #"Merged Queries" = Table.NestedJoin(#"Removed Columns", {"Details.Dependent Bill number "}, #"Dependent number", {"DependentBillNmber"}, "Dependent number", JoinKind.LeftOuter),
    #"Expanded Dependent number" = Table.ExpandTableColumn(#"Merged Queries", "Dependent number", {"State", "Creation date"}, {"Dependent number.State", "Dependent number.Creation date"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Dependent number",{{"Dependent number.State", "State"}, {"Dependent number.Creation date", "Creation date"}}),
    #"Appended Query" = Table.Combine({#"Renamed Columns", BillNumber}),
    #"Renamed Columns1" = Table.RenameColumns(#"Appended Query",{{"Details.Dependent Bill number ", "Dependent Number"}})
in
    #"Renamed Columns1"

I've attached the sample PBIX file





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

5 REPLIES 5
Abbi
Frequent Visitor

@PaulDBrown  Thanks a lot for the help. your solution is resolved my problem.

Abbi
Frequent Visitor

Required output of the above scenario

 

BillNumberDependent Bill number statecreation date
101 success12-Aug
 2001success13-Aug
102 success14-Aug
 2001success13-Aug
 2002success20-Aug
103 success02-Sep
 2003success04-Sep
 2006processing20-Sep
Abbi
Frequent Visitor

@PaulDBrown Kindly help me out on this

Abbi
Frequent Visitor

It's working . Thanks a lot for your help

@Abbi 

See if this works for you:

result.PNG

 I created the table in Power Query using the following code:

let
    Source = Table.NestedJoin(BillNumber, {"BillNumber"}, Details, {"BillNumber "}, "Details", JoinKind.LeftOuter),
    #"Expanded Details" = Table.ExpandTableColumn(Source, "Details", {"Dependent Bill number "}, {"Details.Dependent Bill number "}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Details",{"Creation date", "Description", "State"}),
    #"Merged Queries" = Table.NestedJoin(#"Removed Columns", {"Details.Dependent Bill number "}, #"Dependent number", {"DependentBillNmber"}, "Dependent number", JoinKind.LeftOuter),
    #"Expanded Dependent number" = Table.ExpandTableColumn(#"Merged Queries", "Dependent number", {"State", "Creation date"}, {"Dependent number.State", "Dependent number.Creation date"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Dependent number",{{"Dependent number.State", "State"}, {"Dependent number.Creation date", "Creation date"}}),
    #"Appended Query" = Table.Combine({#"Renamed Columns", BillNumber}),
    #"Renamed Columns1" = Table.RenameColumns(#"Appended Query",{{"Details.Dependent Bill number ", "Dependent Number"}})
in
    #"Renamed Columns1"

I've attached the sample PBIX file





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.