Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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
Proud to be a Super User!
Paul on Linkedin.
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 |
It's working . Thanks a lot for your 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
Proud to be a Super User!
Paul on Linkedin.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.