PowerQuery - Add file name as a column

Question

I'm new to PQ

We are working in a retail environment. For each promotional event, multiple employees work on an excel template and forecast for their SKUs.

I'm trying to merge forecast excel files from multiple employees to get a simple Sku resume.

So for each file, I built a loading template making it ready to merge in the master query.

So far that's fine.

The only thing I'm trying to achieve is to add the file name as a custom column for reference.

Heres the simple code I have for each forecast file.

    Source = Excel.Workbook(File.Contents("C:\Users\aproulx\J.E. Mondou\Promotions - Rapport Logistique Template Alex\12 DÉCEMBRE\Estimés_circulaire_décembre_2020_Gen.xlsx"), null, true),
    EXPORT_Sheet = Source{[Item="Estimés",Kind="Sheet"]}[Data],
   #"Premières lignes supprimées" = Table.Skip(EXPORT_Sheet,4),
    #"En-têtes promus" = Table.PromoteHeaders(#"Premières lignes supprimées"),
    #"Colonnes renommées" = Table.RenameColumns(#"En-têtes promus",{{"Lift", "Lift_PC_MAG"}, {"Estimés (PC)", "Estimés_PC_MAG"}, {"Estimés ($)", "Estimés_$_MAG"}, {"Lift_1", "Lift_PC_WEB"}, {"Estimés (PC)_2", "Estimés_PC_WEB"}, {"Estimés ($)_3", "Estimés_$_WEB"}, {"Lift PC Chaine", "Lift_PC_CHAINE"}, {"Quantités Promo Est. (PC)", "Estimés_PC_CHAINE"}, {"Ventes promos Est. (S)", "Estimés_$_CHAINE"}, {"Marges Promos Est. ($)", "Estimés_MARGES_$_CHAINE"}}),
    #"Colonnes supprimées" = Table.RemoveColumns(#"Colonnes renommées",{"Commentaires/#(lf)Comments", "Évènement/#(lf)Event", "Date début/#(lf)Start date", "Date fin/#(lf)End Date", "Nombre de jour promo", "Description de la promotion/#(lf)Promotion description", "Bonus Buy", "Type de promotion", "Texte SAP", "Description WAK", "# WAK", "Carreau", "Groupe #(lf)d'acheteur", "Groupe d'autorisation", "Ancien #(lf)Mondou Code", "Statut toutes chaînes", "Valeur#(lf)visibilité", "Fournisseur/#(lf)Supplier", "#", "Devise/#(lf)Currency", "Emplacement/#(lf)Position", "Frais de visibilité payé/#(lf)Paid visibility fee", "Option du menu promotionnel/#(lf)Promotionnal menu option", "Frais de menu promotionnel/#(lf)Promotionnal menu fee", "# Accord MP", "Allocation sur ventes/#(lf)Sales allowance ($)", "# Accord", "Allocation sur ventes/#(lf)Sales allowance (Pt câlin/Cuddle points)", "# Accord CÂLIN", "Allocation sur achats/#(lf)Off invoice", "Confirmation", "Rabais #(lf)Promotionnel", "Coûtant #(lf)Promotionnel", "Prix de vente #(lf)Promotionnel", "Marge #(lf)Promotionnelle (%)", "Coûtant de base #(lf)Régulier", "Coûtant net #(lf)Régulier", "Prix de vente#(lf)Régulier", "Marge #(lf)Régulière (%)", "Ventes régulières/Semaine#(lf)(moy. 12 dernières semaines)", "Baseline", "% Web", "Ventes régulières/Semaine#(lf)(moy. 12 dernières semaines)_4", "Baseline_5", "Quantités Reg. (PC)", "Ventes Reg. ($)", "Marges Reg. ($)", "Quantités Lift (PC)", "Ventes Lift ($)", "Marges Lift ($)", "Quantités Lift (%)", "Ventes Lift (%)", "Marges Lift (%)", "Estimés à 0", "Estimés dans rapport logistique", "Identiques?", "Groupe de Marchandise", "Marque", "% SKU", "% G.D.M / MARQUE", "% G.D.M", "3 PREM NIVEAUX", "AVG", "écart-type", "Magasin", "Entrepôt", "Column81"}),
    #"Lignes filtrées" = Table.SelectRows(#"Colonnes supprimées", each ([Mondou Code] <> null))
in
    #"Lignes filtrées"``` 


Thanks so much for the help

Could you please elaborate a bit more exactly where is the error? (if there is any). Could you also elaborate on: "The only thing I'm trying to achieve is to add the file name as a custom colomn for reference." — JFerro

Ricardo Diaz Ricardo Diaz · Accepted Answer · 2020-10-16T02:13:29

Would something like this work?

See FileName in the first line defined and assigned as a variable
Then it's concat with the file path
At the end, it's added as a custom column

It's untested.

Let me know if it works.

let
    FileName = "Estimés_circulaire_décembre_2020_Gen.xlsx",
    Source = Excel.Workbook(File.Contents("C:\Users\aproulx\J.E. Mondou\Promotions - Rapport Logistique Template Alex\12 DÉCEMBRE\" & FileName), null, true),
    EXPORT_Sheet = Source{[Item="Estimés",Kind="Sheet"]}[Data],
    #"Premières lignes supprimées" = Table.Skip(EXPORT_Sheet,4),
    #"En-têtes promus" = Table.PromoteHeaders(#"Premières lignes supprimées"),
    #"Colonnes renommées" = Table.RenameColumns(#"En-têtes promus",{{"Lift", "Lift_PC_MAG"}, {"Estimés (PC)", "Estimés_PC_MAG"}, {"Estimés ($)", "Estimés_$_MAG"}, {"Lift_1", "Lift_PC_WEB"}, {"Estimés (PC)_2", "Estimés_PC_WEB"}, {"Estimés ($)_3", "Estimés_$_WEB"}, {"Lift PC Chaine", "Lift_PC_CHAINE"}, {"Quantités Promo Est. (PC)", "Estimés_PC_CHAINE"}, {"Ventes promos Est. (S)", "Estimés_$_CHAINE"}, {"Marges Promos Est. ($)", "Estimés_MARGES_$_CHAINE"}}),
    #"Colonnes supprimées" = Table.RemoveColumns(#"Colonnes renommées",{"Commentaires/#(lf)Comments", "Évènement/#(lf)Event", "Date début/#(lf)Start date", "Date fin/#(lf)End Date", "Nombre de jour promo", "Description de la promotion/#(lf)Promotion description", "Bonus Buy", "Type de promotion", "Texte SAP", "Description WAK", "# WAK", "Carreau", "Groupe #(lf)d'acheteur", "Groupe d'autorisation", "Ancien #(lf)Mondou Code", "Statut toutes chaînes", "Valeur#(lf)visibilité", "Fournisseur/#(lf)Supplier", "#", "Devise/#(lf)Currency", "Emplacement/#(lf)Position", "Frais de visibilité payé/#(lf)Paid visibility fee", "Option du menu promotionnel/#(lf)Promotionnal menu option", "Frais de menu promotionnel/#(lf)Promotionnal menu fee", "# Accord MP", "Allocation sur ventes/#(lf)Sales allowance ($)", "# Accord", "Allocation sur ventes/#(lf)Sales allowance (Pt câlin/Cuddle points)", "# Accord CÂLIN", "Allocation sur achats/#(lf)Off invoice", "Confirmation", "Rabais #(lf)Promotionnel", "Coûtant #(lf)Promotionnel", "Prix de vente #(lf)Promotionnel", "Marge #(lf)Promotionnelle (%)", "Coûtant de base #(lf)Régulier", "Coûtant net #(lf)Régulier", "Prix de vente#(lf)Régulier", "Marge #(lf)Régulière (%)", "Ventes régulières/Semaine#(lf)(moy. 12 dernières semaines)", "Baseline", "% Web", "Ventes régulières/Semaine#(lf)(moy. 12 dernières semaines)_4", "Baseline_5", "Quantités Reg. (PC)", "Ventes Reg. ($)", "Marges Reg. ($)", "Quantités Lift (PC)", "Ventes Lift ($)", "Marges Lift ($)", "Quantités Lift (%)", "Ventes Lift (%)", "Marges Lift (%)", "Estimés à 0", "Estimés dans rapport logistique", "Identiques?", "Groupe de Marchandise", "Marque", "% SKU", "% G.D.M / MARQUE", "% G.D.M", "3 PREM NIVEAUX", "AVG", "écart-type", "Magasin", "Entrepôt", "Column81"}),
    #"Lignes filtrées" = Table.SelectRows(#"Colonnes supprimées", each ([Mondou Code] <> null)),
    Output = Table.AddColumn(#"Lignes filtrées", "File name", each FileName)
in
    Output

PowerQuery - Add file name as a column

1 Answers