Forum Discussion

Helper I

4 years ago

Solved

Need hep on Matrix table visualization

Hello All,

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

BillNumber	State	Creation date	Description
101	success	12-Aug	Groceries
102	success	14-Aug	furniture
103	success	02-Sep	Bakery
104	fail	16-Sep	Dairy
105	processing	23-Oct	Bakery

DependentBillNmber	State	Creation date	Description
2001	success	13-Aug
2002	success	20-Aug
2003	success	04-Sep
2004	fail	05-Sep
2005	fail	26-Oct
2006	processing	20-Sep
2007	fail	24-Oct
2008	success	25-Oct

BillNumber	Dependent Bill number
101	2001
102	2001
103	2003
104	2005
105
103	2006
104	2008
102	2002

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

BillNumber	Dependent Bill number	state	creation date
101		success	12-Aug
2001	success	13-Aug
102		success	14-Aug
2001	success	13-Aug
2002	success	20-Aug
103		success	02-Sep
2003	success	04-Sep
2006	processing	20-Sep

Please help

See if this works for you:

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

5 Replies

Helper I

Required output of the above scenario

BillNumber	Dependent Bill number	state	creation date
101		success	12-Aug
	2001	success	13-Aug
102		success	14-Aug
	2001	success	13-Aug
	2002	success	20-Aug
103		success	02-Sep
	2003	success	04-Sep
	2006	processing	20-Sep

Helper I

PaulDBrown Kindly help me out on this

Community Champion

See if this works for you:

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

Abbi
Helper I
4 years ago
PaulDBrown Thanks a lot for the help. your solution is resolved my problem.