Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Mpoweruser
New Member

Im triying to create a dynamic Append quarry

HI, 

Im uploading severeal excel sheet from a folder (that containes data from diffrent customers). this folder will be updated each week but not I will not always have same file. So I want to create a dynamic Append quarry. 
this is the line for the "source"
= Table.Combine({#"ASOS UK", #"ASOS US", Fenwick, Harrods, HOF, JLP, Jarrold, #"Space NK", voisins, #"HNIX (2)"})

I created a list with the same names and tried to call to my list instead 
= Table.Combine(#"Main (2)")
but get below error
Expression.Error: We cannot convert the value "ASOS UK" to type Table.
Details:
Value=ASOS UK
Type=[Type]

can someone please help me.
thank you

1 ACCEPTED SOLUTION

The drill-down doesn't work because it's actually drill-down on the text values instead of the objects, however there is no need to drill-down and create a separate list.  Combining the column of tables will be dynamic without this step. It is also unnecessary to have each table as a separate query, unless there are individual transformations that must be applied to each one before they can be combined (there are other ways to handle this too but they are very complex).

If you are simply trying to combine all tables listed in the Main query dynamically, you can do so like what's listed below.  However, if you are trying to combine multiple Excel files, there are extra steps that need to be taken to avoid challenges such as headers being repeated in the combined table.  Power Query provides you with a feature which handles this for you. 

Click the double-arrows button in the Content column header and Power Query will guide you through the process.  This creates a custom function (and invokes the resulting function for you) which lets you define some transformations that should be applied to all files before combining the content, such as promoting the first row to headers.  

jennratten_2-1717676555270.png

This is a good detailed explanation.

https://exceloffthegrid.com/power-query-import-all-files-in-a-folder/ 

 

Alternate (and info that's good to know when you want to combine other kinds of tables).

Notes - Add a new column to convert the binary to Excel.  I have named this new column Data.  Since it is possible for different tables to have different columns/attributes, a best practice is to include a statement which creates a list of all columns in all the listed tables tables before combining the tables (if it is certian that this will not be different, like getting the structure of a list of xlsx files, this can be skipped).  

BinaryToExcel = Table.AddColumn(Main, "Data", each Excel.Workbook ( [Content] )),
GetFileObjects = Table.ExpandTableColumn(BinaryToExcel, "Data", Table.ColumnNames(Table.Combine(BinaryToExcel[Data])))

jennratten_0-1717675771601.png

 This will add a new set of columns to the existing rows which contains the objects from each respective file, from which, you will need to filter for the appropriate file objects, like sheets with specific names, etc. and then combine and expand again.

Table.ExpandTableColumn(GetFileObjects , "Data", Table.ColumnNames(Table.Combine(GetFileObjects [Data])))

jennratten_1-1717676077505.png

As you can see, the data from the sheets/tables has been combined with one hiccup - the header row from each is present in multiple rows of the table.  You will need to now filter those out.

If this post helps to answer your questions, please consider marking it as a solution so others can find it more quickly when faced with a similar challenge.

Proud to be a Microsoft Fabric Super User

View solution in original post

6 REPLIES 6
peterpan
Helper I
Helper I

let
    Source = Folder.Files("C:\Users\rscsh\Desktop\OATesting\ABC\ToSign\Post Sign\Successful"),
    #"Added Custom" = Table.AddColumn(Source, "SheetTable", each Excel.Workbook([Content],  true))    
in
    #"Added Custom"

Try using this and expand the SheetTable column basis the sheets you want. Now use Table.Combine(TableName[Data]) and it'll combine it all

jennratten
Super User
Super User

Hello - The function Table.Combine has an input of a list of tables.  If you have two tables (Table1 and Table2) you can create a list of tables by wrapping them in curly braces.  The tables within this list can be appended to each other by placing it inside Table.Combine.  The error you are getting says that one of the items in your list of tables is text instead of a table.  It looks like your second refrence for the table ASOS UK is just missing the number symbol.  Please try adding that. Without it, it is simply reading in a text value of ASOS UK instead of the table variable.

jennratten_0-1717586192224.png

 

If this post helps to answer your questions, please consider marking it as a solution so others can find it more quickly when faced with a similar challenge.

Proud to be a Microsoft Fabric Super User

as you can see below I did adjust the name on my list to match my tables 

Mpoweruser_1-1717603162325.png

when I replace the list in my Table.Combine from 

= Table.Combine({ASOSUK, ASOSUS, Fenwick, Harrods, HOF, JLP, Jarrold, SpaceNK, voisins, HNIXfinal})
to

= Table.Combine(#"Main (2)") (im calling my list)
I still get 
Expression.Error: We cannot convert the value "ASOSUK" to type Table.
Details:
Value=ASOSUK
Type=[Type]


I see - it is not working for you because your list is a list of text values instead of a list of tables.  How did you create your list? If you manually typed it, then you will need to refer to each table object as a table to create your list, like so:

 

 

TableList = { ASOSUK, ASOUS, Fenwick, HARRODS, HOF, JarroId, JLP, SpaceNK, Viosins }

 

Then you can combine it like this:

 

Append = Table.Combine ( TableList )

 

I see in your snip that you are replacing some of the text in what seems to be a table name.  That will only work on text.  If you are using some logic to conditionally retrieve the list of tables then please share that logic so I can further assist.

If this post helps to answer your questions, please consider marking it as a solution so others can find it more quickly when faced with a similar challenge.

Proud to be a Microsoft Fabric Super User

I get my list by pressing "Drill down" on my "main" table. same surce that I create my tables from.

Mpoweruser_0-1717662932632.png

for example my ASOSUK source is the same .

Mpoweruser_1-1717663002195.png

my original sorce is a folder that we same customer files to each week.
how can I create a dynamic list? I want to be able to combine my file undependent on which customer have shared thier file in a specif week.

The drill-down doesn't work because it's actually drill-down on the text values instead of the objects, however there is no need to drill-down and create a separate list.  Combining the column of tables will be dynamic without this step. It is also unnecessary to have each table as a separate query, unless there are individual transformations that must be applied to each one before they can be combined (there are other ways to handle this too but they are very complex).

If you are simply trying to combine all tables listed in the Main query dynamically, you can do so like what's listed below.  However, if you are trying to combine multiple Excel files, there are extra steps that need to be taken to avoid challenges such as headers being repeated in the combined table.  Power Query provides you with a feature which handles this for you. 

Click the double-arrows button in the Content column header and Power Query will guide you through the process.  This creates a custom function (and invokes the resulting function for you) which lets you define some transformations that should be applied to all files before combining the content, such as promoting the first row to headers.  

jennratten_2-1717676555270.png

This is a good detailed explanation.

https://exceloffthegrid.com/power-query-import-all-files-in-a-folder/ 

 

Alternate (and info that's good to know when you want to combine other kinds of tables).

Notes - Add a new column to convert the binary to Excel.  I have named this new column Data.  Since it is possible for different tables to have different columns/attributes, a best practice is to include a statement which creates a list of all columns in all the listed tables tables before combining the tables (if it is certian that this will not be different, like getting the structure of a list of xlsx files, this can be skipped).  

BinaryToExcel = Table.AddColumn(Main, "Data", each Excel.Workbook ( [Content] )),
GetFileObjects = Table.ExpandTableColumn(BinaryToExcel, "Data", Table.ColumnNames(Table.Combine(BinaryToExcel[Data])))

jennratten_0-1717675771601.png

 This will add a new set of columns to the existing rows which contains the objects from each respective file, from which, you will need to filter for the appropriate file objects, like sheets with specific names, etc. and then combine and expand again.

Table.ExpandTableColumn(GetFileObjects , "Data", Table.ColumnNames(Table.Combine(GetFileObjects [Data])))

jennratten_1-1717676077505.png

As you can see, the data from the sheets/tables has been combined with one hiccup - the header row from each is present in multiple rows of the table.  You will need to now filter those out.

If this post helps to answer your questions, please consider marking it as a solution so others can find it more quickly when faced with a similar challenge.

Proud to be a Microsoft Fabric Super User

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.