Need to add the same column to multiple tables

[kmatherly]

Hello, 

I have several dozen tables that I've imported from Excel. They each represent a different event, but the event id information does not exist in the table itself, only in the tablename. (which yes, was poor planning, but can't be fixed at this point, since for a variety of complicated and tedious reasons  I can't modify the files and then re-import them.) 

I would like to programmatically add an eventid column to each table that contains the table name, so that I can then join all of the tables together and run analytics on them as a whole. Is this possible or will I need to add the columns manually? 

Thank you for any help or insight you can provide. 

-Kristin

[parry2k]

@kmatherly are these sheets/tables in one excel file and you want table name as part of column? There are many solutions to achieve this but would like to know how you are connecting to a sheet/table? it is one excel file or multiple excel file etc etc?

[kmatherly]

Hello, 

Unfortunately, they are in different files. And I can't make any modifications to the tables. All the work has to be done in Power BI. So, I need to add a column to each table that has the value for each row that equals the table name. 

Does that make sense? 

Thanks,

Kristin

[parry2k]

@kmatherly ok, it is fine, it will be taken care in power bi. Can you share code of one of the table by going to query editor -> select table on left hand side pane and click advanced editor in the menu. Copy the code and paste it here. Just want to see how you are reading from excel file and that will help to get you the solution.

[kmatherly]

Thank you for your help.  Here it is. 

let
Source = Csv.Document(File.Contents("G:\IS\IS Teamwork\DST files\Unit Working Files\BrandMarketing\PostcardAnalysisFall2019\RawFiles\LandL\lhenning_LLOLC_Postcards_hd42981_073018.txt"),[Delimiter=" ", Columns=11, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Full Name", type text}, {"Organization", type text}, {"Address 1", type text}, {"Address 2", type text}, {"Address 3", type text}, {"City", type text}, {"State", type text}, {"Zip", type text}, {"Last Name", type text}, {"First Name", type text}, {"CustomerID", Int64.Type}})
in
#"Changed Type"

[parry2k]

@kmatherly first and foremost, it is not you are connected to excel, it is csv file. Shoot me an email directly and we can have quick go to session and I can walk you thru how to do it?

[parry2k · Accepted solution]

@kmatherly well you have the solution, for now add column manually . Cheers!!