Merge two tables, where duplication of rows are needed

[Anonymous]

Hi,

I have two tables, one is the vacancy rates of a portfolio (Table 1) and a second one with the benchmark data (Table 2).

Table 1: Portfolio

year	street	municipality	unit	vacancy
2018	Marktgasse 2	Kloten	Kloten (Pfo)	2.50%
2019	Marktgasse 2	Kloten	Kloten (Pfo)	2.25%
2018	Flurstr. 7	Kloten	Kloten (Pfo)	3.00%
2019	Flurstr. 7	Kloten	Kloten (Pfo)	2.70%
2018	Freulerstr. 5	Allschwil	Allschwil (Pfo)	4.50%
2019	Freulerstr. 5	Allschwil	Allschwil (Pfo)	3.90%

Table 2: Benchmark Data 

year	municipality	unit	vacancy
2017	Allschwil	Unteres Baselbiet (MS)	0.67%
2018	Allschwil	Unteres Baselbiet (MS)	0.86%
2019	Allschwil	Unteres Baselbiet (MS)	1.08%
2017	Allschwil	Allschwil (Mun)	0.89%
2018	Allschwil	Allschwil (Mun)	1.08%
2019	Allschwil	Allschwil (Mun)	1.10%
2017	Kloten	Glattal-Furttal (MS)	0.85%
2018	Kloten	Glattal-Furttal (MS)	1.05%
2019	Kloten	Glattal-Furttal (MS)	0.77%
2017	Kloten	Kloten (Mun)	0.57%
2018	Kloten	Kloten (Mun)	0.87%
2019	Kloten	Kloten (Mun)	0.88%

Now, I want to merge the tables to get the follwing result:

year	street	municipality	unit	vacancy
2017	Marktgasse 2	Kloten	Glattal-Furttal (MS)	0.85%
2017	Marktgasse 2	Kloten	Kloten (Mun)	0.57%
2018	Marktgasse 2	Kloten	Kloten (Pfo)	2.50%
2018	Marktgasse 2	Kloten	Glattal-Furttal (MS)	1.05%
2018	Marktgasse 2	Kloten	Kloten (Mun)	0.87%
2019	Marktgasse 2	Kloten	Kloten (Pfo)	2.25%
2019	Marktgasse 2	Kloten	Glattal-Furttal (MS)	0.77%
2019	Marktgasse 2	Kloten	Kloten (Mun)	0.88%
2017	Flurstr. 7	Kloten	Glattal-Furttal (MS)	0.85%
2017	Flurstr. 7	Kloten	Kloten (Mun)	0.57%
2018	Flurstr. 7	Kloten	Kloten (Pfo)	3.00%
2018	Flurstr. 7	Kloten	Glattal-Furttal (MS)	1.05%
2018	Flurstr. 7	Kloten	Kloten (Mun)	0.87%
2019	Flurstr. 7	Kloten	Kloten (Pfo)	2.70%
2019	Flurstr. 7	Kloten	Glattal-Furttal (MS)	0.77%
2019	Flurstr. 7	Kloten	Kloten (Mun)	0.88%

I didn't show the case for Allschwil, but I think you get what I mean. This structure is needed, so I can show a Line Chart with the vacancy rate. When I select Kloten, Marktgasse 2, then the vacancy rates of the portfolio and the corresponding Benchmarks should be displayed. I tried it different ways but so far with no success. Many thanks for your help!

[ImkeF]

Hi @Anonymous 

this would be pretty easy if both tables would cover the same years.

let
    Source = Table.NestedJoin(Portfolio, {"year", "municipality"}, #"Benchmark Data" , {"year", "municipality"}, "Benchmark Data", JoinKind.LeftOuter),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Table.FromRecords( { _ } ) & [Benchmark Data]),
    Custom1 = Table.Combine( #"Added Custom"[Custom] ),
    #"Filled Down" = Table.FillDown(Custom1,{"street"}),
    #"Removed Columns" = Table.RemoveColumns(#"Filled Down",{"Benchmark Data"})
in
    #"Removed Columns"

Please come back if 2017 shall be included as well (although there is no portfolio-data for it? )

[Anonymous]

Hi @ImkeF 

many thanks for your reply. As you anticipated correctly, we need 2017 as well and there is no portfolio data for 2017. We would like to show how the market evolved and actually have data back to 2012, but for simplicity I only added 2017. I already tried to do a merge and fill up the missing address but this doesn't work for properties wich are in the same municipality. Already tried to create a key with years and municipality but that didn't work neither.

many thanks for your help!

Bea

[ImkeF]

Hi @Anonymous 

how about this then?: 

let
    Start = Portfolio,
    #"Grouped Rows" = Table.Group(Start, {"street", "municipality", "unit"}, {{"Object", each _, type table [year=number, street=text, municipality=text, unit=text, vacancy=number]}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.NestedJoin([Object], {"year","municipality"}, #"Benchmark Data" , {"year","municipality"}, "Benchmark Data", JoinKind.FullOuter)),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.1", each Table.AddColumn([Custom], "Custom", (x) => Table.FromRecords( { x } ) & x[Benchmark Data])),
    Custom1 = Table.Combine( Table.Combine( #"Added Custom2"[Custom.1] ) [Custom] ),
    #"Filtered Rows" = Table.SelectRows(Custom1, each ([unit] <> null)),
    #"Filled Down" = Table.FillDown(#"Filtered Rows",{"street"}),
    #"Removed Columns" = Table.RemoveColumns(#"Filled Down",{"Benchmark Data"})
in
    #"Removed Columns"

[Anonymous]

Thank you very much @ImkeF 

Unfortunately, it didn't work properly. I think the problem is that the fill down is only working properly, when the first row with the "street" data is in the right place.  With your code I got the following result:

year	street	municipality	unit	vacancy
2017	null	Kloten	Glattal-Furttal (MS)	0.85%
2017	null	Allschwil	Allschwil (Mun)	0.89%
2018	null	Allschwil	Unteres Baselbiet (MS)	0.86%
2018	null	Allschwil	Allschwil (Mun)	1.08%
2017	null	Allschwil	Unteres Baselbiet (MS)	0.67%
2017	null	Kloten	Kloten (Mun)	0.57%
2019	null	Allschwil	Allschwil (Mun)	1.10%
2019	null	Allschwil	Unteres Baselbiet (MS)	1.08%
2018	Flurstrasse 7	Kloten	Kloten (Pfo)	3.00%

That was exactly the problem I had when I tried to do it with the fill down. Maybe you have an idea how to fix this?

[ImkeF]

Hi @Anonymous  

this is hard to understand, as I'm starting with the table incl. streets (on the left side) and merge tables (to the right side).

So when expanding, the fill down should work properly. Any chance you might have a applied my code in a different way?

Attaching the file to follow along.

[Anonymous]

Hi @ImkeF 

thank you for sharing your PBI. Now I see, why it worked for you. In the expected result I plotted, I omitted Allschwil for simplicity reasons, but I would expect that the code works for all Portfolio entries and not only for Kloten. 

The point of this excercise is to automate the process, for e.g. when we have a portfolio of another client with different locations it should automatically merge it to the Benchmarks.

[ImkeF]

Hi @Anonymous 

if you check query "Portfolio" in my file, you'll see that I explicitely filtered on "Kloten" to compare like for like:

The problem with your sample data is that the input data and your sample of the desired results are not compatible.

I would expect my solution to work on your full data as well.

If it doesn't, please adjust your sample data so that I can adjust my file accordingly.

Thanks.

[Anonymous]

@ImkeF 

I did see that you filtered for Kloten, but whithout the filter you get the same result as I posted earlier. 

I once tried a solution where you first map the municipality of the portfolio with the Benchmark data, so an additional column in the Benchmark file, where the municipalities which are in portfolio gets a one. But then I got stack.

[ImkeF · Accepted solution]

Hi

oh yes, I see that now (proper sample data would have helped, tbh..)

More operations have to be applied on the aggregated level:

let
    Start = Portfolio,
    #"Grouped Rows" = Table.Group(Start, {"street", "municipality", "unit"}, {{"Object", each _, type table [year=number, street=text, municipality=text, unit=text, vacancy=number]}}),
    SelectBenchmark = Table.AddColumn(#"Grouped Rows", "BenchmarkData", each Table.SelectRows(#"Benchmark Data", (x) => x[municipality] = [municipality])),
    MergeOnYear = Table.AddColumn(SelectBenchmark, "MergedBenchmark", each Table.NestedJoin([Object], {"year"}, [BenchmarkData] , {"year"}, "Benchmark Data", JoinKind.FullOuter)),
    Combine = Table.AddColumn(MergeOnYear, "Custom.1", each Table.AddColumn([MergedBenchmark], "Custom", (x) => Table.FromRecords( { x } ) & x[Benchmark Data])),
    ExpandCustom = Table.AddColumn(Combine, "Custom.2", each Table.Combine( [Custom.1]  [Custom] )),
    FillDown = Table.AddColumn(ExpandCustom, "Custom.3", each Table.FillDown([Custom.2],{"street"})),
    Custom2 = Table.Combine ( FillDown[Custom.3] ),
    #"Filtered Rows" = Table.SelectRows(Custom2, each ([unit] <> null)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Benchmark Data"})
in
    #"Removed Columns"

Please check attached file.

[Anonymous]

Hi @ImkeF 

you're my star! Thank you so much for your help, very much appreciated!

[Greg_Deckler]

@ImkeF