Forum Discussion
Abbi
4 years agoHelper I
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...
- 4 years ago
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
4 years agoHelper 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 |
- Abbi4 years agoHelper I
PaulDBrown Kindly help me out on this
- PaulDBrown4 years agoCommunity 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
- Abbi4 years agoHelper I
It's working . Thanks a lot for your help