Combining tables...

[clarky4]

I've started down the Power Query rabbit hole, and need help with a problem...

I'm trying to get all combinations from several tables using a full outer join as outlined here. The described solution works well when all tables have at least one record, but I cannot get it to work when one of my tables is updated to contain zero records.

My tables look something like this:

table1	table2	table3	table4
a	d		g
b	e		h
c	f

Notice that each table contains between 0 to 3 records, and table3 has zero records in this example.

Is it possible to tell Power Query to ignore table(s) with no records? Remember, table3 may have no records this time, but table2 may have no records next time.

I look forward to your solutions. Thanks!

 

[sandy666]

are you trying Table.Combine or Table.NestedJoin ?

• if the first - just rename headers to the same name each then use Append Queries
• if the second - add Index to each table then use Merge Queries with JoinKind.LeftOuter
• if you don't want empty table just don't use it

table1	table2	table3	table4		table1	table2	table3	table4
a	d		g		a	d		g
b	e		h		b	e		h
c	f				c	f

Table
a
b
c
d
e
f
g
h

 

[clarky4]

are you trying Table.Combine or Table.NestedJoin ?

• if the first - just rename headers to the same name each then use Append Queries
• if the second - add Index to each table then use Merge Queries with JoinKind.LeftOuter
• if you don't want empty table just don't use it

table1	table2	table3	table4		table1	table2	table3	table4
a	d		g		a	d		g
b	e		h		b	e		h
c	f				c	f

Table
a
b
c
d
e
f
g
h

I'm using the second solution - titled "Produce All Unique Combinations – Using A Formula" - from the webpage I provided in my OP.

When combining the first two tables for example, the expectation would be something like this:

Column1	Column1.1
a	d
a	e
a	f
b	d
b	e
b	f
c	d
c	e
c	f

Obviously, combining additional tables will create more combinations, but when a table with no records is combined the query breaks.

I hope this clarifies.

 

[sandy666]

sure, try this

and next add Custom Column with filtered Table4

but if you want table with blank rows you can add all tables this way without any filtering

result without blank rows

 

[clarky4]

Is there a way to do this automatically, as opposed to manually omitting the empty table?

 

[sandy666]

you can try with
Source = Excel.CurrentWorkbook()
but this is not a Click&Go

for more info:

Power Query documentation - Power Query

Power Query is used to filter, combine, and mash up data from one or more supported data sources.

docs.microsoft.com