Combine Tables from CurrentWorkbook

[hwoehrle]

Hi,

i'm trying to combine selected Tables from my current Workbook. For Example:

[Worksheet AW01]

Table "Ref_AW01":

Row	DataText	Value1
1	AAAAA	0,7
2	BBBBB	2,3

[Worksheet AW02]

Table "Ref_AW02":

Row	DataText	Value1
1	CCCCC	0,01
2	DDDD	1,2
3	EEEEE	5,6

I like to combine all Tables in the Workbook named like "Ref_*" to

Source	Row	DataText	Value1
AW01	1	AAAAA	0,7
AW01	2	BBBBB	2,3
AW02	1	CCCCC	0,01
AW02	2	DDDD	1,2
AW02	3	EEEEE	5,6
..... AW9834

So i tried:

let
  Quelle = Excel.CurrentWorkbook(),
  SelTables = Table.SelectRows(Quelle, each Text.StartsWith([Name], "Ref_")),
  CombTable=Table.Combine(SelTables[Name])
in
  CombTable

This raises the follwoing Error:

Expression.Error: Der Wert ""Ref_AW01"" kann nicht in den Typ "Table" konvertiert werden.  ~> The value "Ref_AW01"" can't be converted to type "Table"
Details:
Value=Ref_AW01
Type=[Type]

The other Question is, how can i add a column in each Table named like the Table as Source-Reference?

Thanks in advance for any hints and tips!

Best regards,

Heiko

[Vijay_A_Verma]

You need to change the statement before in to (hence only change is from Name to Content)

CombTable=Table.Combine(SelTables[Content])

Hence, final code will be 

let
  Quelle = Excel.CurrentWorkbook(),
  SelTables = Table.SelectRows(Quelle, each Text.StartsWith([Name], "Ref_")),
  CombTable=Table.Combine(SelTables[Content])
in
  CombTable

[donsvensen]

Hi 

If you use Table.ExpandTableColumn that might work

If you want to use Table.Combine you have to specify the name of the column with the Tables 

= Table.Combine( #"Filtered Rows"[Content])

/Erik

[hwoehrle]

Hi Erik,

thanks for your reply!

Since there are many tables (with quite a lot columns) i want to combine, i guess the Table.ExpandTableColumn won't do the job.

With TableCombine i tried to combine all the selected Tables -> SelTables

CombTable=Table.Combine(SelTables[Name]) as you suggested. Or i misunderstand you.

Maybe you can take a look at the example file?

SampleFile 

[donsvensen · Accepted solution]

Hi 

The column containing the data in your sample file is called [Content] and not [Name] so the query shoul be like this

let
  Quelle = Excel.CurrentWorkbook(),
  SelTables = Table.SelectRows(Quelle, each Text.StartsWith([Name], "Ref_")),
  CombTable=Table.Combine(SelTables[Content])
in
  CombTable 

Hope this helps you 

/Erik