topic Re: Need hep on Matrix table visualization in Custom Visuals Development Discussion

Need hep on Matrix table visualization

Abbi — Tue, 23 Nov 2021 07:58:33 GMT

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

Re: Need hep on Matrix table visualization

Abbi — Tue, 23 Nov 2021 08:02:54 GMT

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

Re: Need hep on Matrix table visualization

Abbi — Thu, 25 Nov 2021 06:06:13 GMT

@PaulDBrown Kindly help me out on this

Re: Need hep on Matrix table visualization

PaulDBrown — Thu, 25 Nov 2021 13:00:41 GMT

@Abbi

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

Re: Need hep on Matrix table visualization

Abbi — Thu, 25 Nov 2021 17:34:20 GMT

It's working . Thanks a lot for your help

Re: Need hep on Matrix table visualization

Abbi — Thu, 25 Nov 2021 17:37:40 GMT

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