Forum Discussion
Inserting the table name into a custom column
Hi. I want to join a number of tables using append but still be able to easily identify which rows came from which table. My initial thought is to just create a custom column in each table containing that table's name but I can't see how to do this. Any help would be much appreciated.
You do that beforehand. Most of the time you have a list of files in a SharePoint folder that have the same structure.
You add a custom column to each row with the file contents.
Then you drop all other columns except the file name and the new column.
Now when you expand the new column you have all the columns inside these files plus the filename column.
Hi Anonymous ,
Thank you for reaching out to the Microsoft Fabric Community.
The solution which lbendlin mentioned is absolutely correct and in addition to that, if you're combining multiple tables from files stored in a SharePoint folder or any folder-based data source, Power Query automatically generates a Name column containing each file's name. By keeping only the Name and Content columns before expanding the content, you'll have a seamless way to identify which rows come from which file. Once the content is expanded, the file name will act as a unique identifier for each row.
This approach is dynamic, simple, and scalable, ensuring that any new files added to the folder will automatically be included without additional effort.
Hope my suggestions give you good idea, if you need any further assistance, feel free to reach out.
If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.
Thank you.
2 Replies
You do that beforehand. Most of the time you have a list of files in a SharePoint folder that have the same structure.
You add a custom column to each row with the file contents.
Then you drop all other columns except the file name and the new column.
Now when you expand the new column you have all the columns inside these files plus the filename column.
Hi Anonymous ,
Thank you for reaching out to the Microsoft Fabric Community.
The solution which lbendlin mentioned is absolutely correct and in addition to that, if you're combining multiple tables from files stored in a SharePoint folder or any folder-based data source, Power Query automatically generates a Name column containing each file's name. By keeping only the Name and Content columns before expanding the content, you'll have a seamless way to identify which rows come from which file. Once the content is expanded, the file name will act as a unique identifier for each row.
This approach is dynamic, simple, and scalable, ensuring that any new files added to the folder will automatically be included without additional effort.
Hope my suggestions give you good idea, if you need any further assistance, feel free to reach out.
If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.
Thank you.