Forum Discussion

Abbi's avatar
Abbi
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...
  • PaulDBrown's avatar
    PaulDBrown
    4 years ago

    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