SOLVED

power query add column with file name

Frequent Contributor

hi,

can someone help me :

 

1) adding the appropiate command so that i get in the final excel chart the filename for eacht line of data?

2) explaining why is this line used for "#"Invocar función personalizada1" = Table.AddColumn(#"Otras columnas quitadas", "Transformar archivo (4)", each #"Transformar archivo (4)"([Content])),"?

 

 

let
Origen = Folder.Files("D:\Individual"),
#"Otras columnas quitadas" = Table.SelectColumns(Origen,{"Content"}),
#"Invocar función personalizada1" = Table.AddColumn(#"Otras columnas quitadas", "Transformar archivo (4)", each #"Transformar archivo (4)"([Content])),
#"Otras columnas quitadas1" = Table.SelectColumns(#"Invocar función personalizada1", {"Transformar archivo (4)"}),
#"Columna de tabla expandida1" = Table.ExpandTableColumn(#"Otras columnas quitadas1", "Transformar archivo (4)", Table.ColumnNames(#"Transformar archivo (4)"(#"Archivo de ejemplo (5)"))),
#"Tipo cambiado" = Table.TransformColumnTypes(#"Columna de tabla expandida1",{{"Column1", type any}, {"Column2", type text}, {"date", type any}, {"saisi", type any}, {"Column5", type text}, {"Column6", type text}, {"Column7", type any}, {"Column8", type text}, {"Column9", type text}, {"Column10", type any}, {"Column11", type any}, {"Column12", type number}, {"Column13", type any}, {"Column14", type text}, {"Column15", type any}, {"Column16", type text}, {"Column17", type number}, {"Column18", type any}, {"Column19", type any}}),
#"Filas filtradas" = Table.SelectRows(#"Tipo cambiado", each ([date] <> null)),
#"Otras columnas quitadas2" = Table.SelectColumns(#"Filas filtradas",{"Column1", "Column2", "date", "saisi", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11"}),
#"Índice agregado" = Table.AddIndexColumn(#"Otras columnas quitadas2", "Índice", 0, 1, Int64.Type),
#"Filas filtradas1" = Table.SelectRows(#"Índice agregado", each ([saisi] <> null)),
#"Texto recortado" = Table.TransformColumns(#"Filas filtradas1",{{"Column8", Text.Trim, type text}, {"Column9", Text.Trim, type text}})
in
#"Texto recortado"

 

thank you, juan

10 Replies

@juan jimenez When you connect to a folder and load the data for the first time, PQ creates some helper queries and a function that contains the necessary steps to connect to each file in the folder (in simple terms). The main query connects to the specified folder and and invokes (executes) the function and expands the data from each table into one larger table.

The first column is called "Source.Name" (in English) and contains the name of the source file. So if you do not delete that column the file name is part of the data set.

Screenshot 2021-12-06 at 08.50.03.png

@Riny_van_Eekelen

Thank you very much for the answer and explanation.

I have not deleted the file name column but when I get the data from the files and I cannot see in column with that file name. 

What instructions should I use in order to get the file named into the final chart?

Thank you, Juan

 

 

@juan jimenez Sorry! Can't help without access to your system.

Is it not possible to add a line to the above commands show that the file name appears in the final data?
If this is the case, could it be possible that I sent the Excel with the power query? How?
Thanks, juan

@juan jimenez 

Check you script. After Invoke Custom Function step script automatically adds Remove Other Columns. It removes all columns but Transform File one. Manually add Name column to the selection.

@juan jimenez 

That is this step

image.png

Sorry for being so useless but I have tried it unsuccessfully. Could you help chaging what it has to be done in the above commands so that I copy paste in my power query editor?
best response confirmed by juan jimenez (Frequent Contributor)
Solution

@juan jimenez 

That's bit hard to do since I don't recognize names you use. If by steps:

Check generated master query which shall be in Other Queries

image.png

find Removed Other Columns1 step and click on gear.

Transform File column is always selected, select Source.Name and, if necessary, any other column(s) with file metadata. Click Ok.

image.png

Please note, Power Query could add this column automatically. Perhaps depends on version, not 100% sure.

Thank you. Very helpful.
My excel version is 2016. So adding to the final chart the file name column is not available on the ribbon.
Can you help and tell me
how could I do it manually?

@juan jimenez 

Sorry, I don't remember what s in 2016. Which of steps from previous you can't reproduce?