Forum Discussion

hwoehrle's avatar
hwoehrle
Frequent Visitor
4 years ago
Solved

Combine Tables from CurrentWorkbook

Hi,

 

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

 

[Worksheet AW01]

Table "Ref_AW01":

Row

DataTextValue1 
1AAAAA0,7 
2BBBBB2,3 

 

[Worksheet AW02]

Table "Ref_AW02":

Row

DataTextValue1 
1CCCCC0,01 
2DDDD1,2 
3EEEEE5,6 

 

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

Source

 

Row

DataTextValue1
AW011AAAAA0,7
AW012BBBBB2,3
AW021CCCCC0,01
AW022DDDD1,2
AW023EEEEE5,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

Need Help
  • donsvensen's avatar
    donsvensen
    4 years ago

    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

4 Replies

  • donsvensen's avatar
    donsvensen
    Skilled Sharer
    4 years ago

    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's avatar
      hwoehrle
      Frequent Visitor
      4 years ago

      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's avatar
        donsvensen
        Skilled Sharer
        4 years ago

        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

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional
    4 years ago

    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