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
PaulDBrown Kindly help me out on this
PaulDBrown
4 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