Waterfall diagram with values extracted from multiple tables

[Lopez0090]

I would like to create a waterfall diagram using data from three tables as follows.

To accomplish this, we would probably have to merge the three tables into a single table, but I don't know how to create that.

Below is an image of the completed waterfall diagram when April 2022 and March 2022 selected.

------------------------------------------------------------------------------------------------------------------

The data used and the relationships created are as follows.

How can I create a waterfall diagram using Table1, Table2, and Table3?
If anyone has any good ideas or methods, please let me know.

A sample report is also attached.

https://www.dropbox.com/s/pzdr1933huscgvl/Sample_WF.pbix?dl=0 

Best regards, Lopez

[Anonymous · Accepted solution]

Hi @Lopez0090 ,

You may go to Power Query Editor, use "Merge Queries" to combine the three tables:

let
    Source = Table.NestedJoin(#"Table 2", {"CustomerNum"}, #"Table 1", {"CustomerNum"}, "Table 1", JoinKind.LeftOuter),
    #"Merged Queries" = Table.NestedJoin(Source, {"CustomerNum"}, #"Table 3", {"CustomerNum"}, "Table 3", JoinKind.LeftOuter),
    #"Expanded Table 1" = Table.ExpandTableColumn(#"Merged Queries", "Table 1", {"Code", "Date"}, {"Code", "Date"}),
    #"Expanded Table 3" = Table.ExpandTableColumn(#"Expanded Table 1", "Table 3", {"Value", "Contract_date"}, {"Value.1", "Contract_date"})
in
    #"Expanded Table 3"

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.