Good morning everyone,
I'm a beginner in Power Query and I've been trying to do the next without success :
I have 2 CVS files with the same first line (header) I want to load from a specific file. When I'm in the editor, I'm setting the first line as the header (corresponding to the first line of my first file) and of cours I don't the first line of the second file to be repeated. So what I did, is after setting the first line as the header, I right click on the table iconin the top left corner of the data preview and I removed errors. It works and deleted well the first line of my second file. Here's the code :
let
Source = Folder.Files("C:\Users\victoire.chantriaux\OneDrive - Campofrio Food Group\Desktop\source"),
#"Fichiers masqués filtrés1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Appeler une fonction personnalisée1" = Table.AddColumn(#"Fichiers masqués filtrés1", "Transformer le fichier à partir de source", each #"Transformer le fichier à partir de source"([Content])),
#"Colonnes renommées1" = Table.RenameColumns(#"Appeler une fonction personnalisée1", {"Name", "Source.Name"}),
#"Autres colonnes supprimées1" = Table.SelectColumns(#"Colonnes renommées1", {"Source.Name", "Transformer le fichier à partir de source"}),
#"Colonne de tables développée1" = Table.ExpandTableColumn(#"Autres colonnes supprimées1", "Transformer le fichier à partir de source", Table.ColumnNames(#"Transformer le fichier à partir de source"(#"Exemple de fichier"))),
#"Type modifié" = Table.TransformColumnTypes(#"Colonne de tables développée1",{{"Source.Name", type text}, {"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type text}, {"Column15", type text}, {"Column16", type text}, {"Column17", type text}, {"Column18", type text}, {"Column19", type text}, {"Column20", type text}, {"Column21", type text}, {"Column22", type text}, {"Column23", type text}, {"Column24", type text}, {"Column25", type text}, {"Column26", type text}, {"Column27", type text}, {"Column28", type text}, {"Column29", type text}, {"Column30", type text}, {"Column31", type text}, {"Column32", type text}, {"Column33", type text}, {"Column34", type text}, {"Column35", type text}, {"Column36", type text}, {"Column37", type text}, {"Column38", type text}, {"Column39", type text}, {"Column40", type text}, {"Column41", type text}, {"Column42", type text}, {"Column43", type text}, {"Column44", type text}, {"Column45", type text}, {"Column46", type text}, {"Column47", type text}, {"Column48", type text}, {"Column49", type text}, {"Column50", type text}, {"Column51", type text}, {"Column52", type text}, {"Column53", type text}, {"Column54", type text}, {"Column55", type text}, {"Column56", type text}, {"Column57", type text}, {"Column58", type text}, {"Column59", type text}, {"Column60", type text}, {"Column61", type text}, {"Column62", type text}, {"Column63", type text}, {"Column64", type text}, {"Column65", type text}, {"Column66", type text}, {"Column67", type text}, {"Column68", type text}, {"Column69", type text}, {"Column70", type text}, {"Column71", type text}, {"Column72", type text}, {"Column73", type text}, {"Column74", type text}, {"Column75", type text}, {"Column76", type text}, {"Column77", type text}, {"Column78", type text}, {"Column79", type text}, {"Column80", type text}, {"Column81", type text}, {"Column82", type text}, {"Column83", type text}, {"Column84", type text}, {"Column85", type text}, {"Column86", type text}, {"Column87", type text}, {"Column88", type text}, {"Column89", type text}, {"Column90", type text}, {"Column91", type text}, {"Column92", type text}, {"Column93", type text}, {"Column94", type text}, {"Column95", type text}, {"Column96", type text}, {"Column97", type text}, {"Column98", type text}, {"Column99", type text}, {"Column100", type text}, {"Column101", type text}, {"Column102", type text}, {"Column103", type text}, {"Column104", type text}, {"Column105", type text}, {"Column106", type text}, {"Column107", type text}, {"Column108", type text}, {"Column109", type text}, {"Column110", type text}, {"Column111", type text}, {"Column112", type text}, {"Column113", type text}, {"Column114", type text}, {"Column115", type text}, {"Column116", type text}, {"Column117", type text}, {"Column118", type text}, {"Column119", type text}, {"Column120", type text}, {"Column121", type text}, {"Column122", type text}, {"Column123", type text}, {"Column124", type text}, {"Column125", type any}}),
#"Colonnes supprimées" = Table.RemoveColumns(#"Type modifié",{"Source.Name"}),
#"Valeur remplacée" = Table.ReplaceValue(#"Colonnes supprimées","'","",Replacer.ReplaceText,{"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27", "Column28", "Column29", "Column30", "Column31", "Column32", "Column33", "Column34", "Column35", "Column36", "Column37", "Column38", "Column39", "Column40", "Column41", "Column42", "Column43", "Column44", "Column45", "Column46", "Column47", "Column48", "Column49", "Column50", "Column51", "Column52", "Column53", "Column54", "Column55", "Column56", "Column57", "Column58", "Column59", "Column60", "Column61", "Column62", "Column63", "Column64", "Column65", "Column66", "Column67", "Column68", "Column69", "Column70", "Column71", "Column72", "Column73", "Column74", "Column75", "Column76", "Column77", "Column78", "Column79", "Column80", "Column81", "Column82", "Column83", "Column84", "Column85", "Column86", "Column87", "Column88", "Column89", "Column90", "Column91", "Column92", "Column93", "Column94", "Column95", "Column96", "Column97", "Column98", "Column99", "Column100", "Column101", "Column102", "Column103", "Column104", "Column105", "Column106", "Column107", "Column108", "Column109", "Column110", "Column111", "Column112", "Column113", "Column114", "Column115", "Column116", "Column117", "Column118", "Column119", "Column120", "Column121", "Column122", "Column123", "Column124"}),
#"En-têtes promus" = Table.PromoteHeaders(#"Valeur remplacée", [PromoteAllScalars=true]),
#"Type modifié1" = Table.TransformColumnTypes(#"En-têtes promus",{{"Site", type text}, {"Product Category", type text}, {"Product Sub-Category", type text}, {"Local Subfamily", type text}, {"Product Group", Int64.Type}, {"", type text}, {"Primary Logistic SKU", Int64.Type}, {"Material", Int64.Type}, {"_1", type text}, {"Brand Type AOSTE", type text}, {"Brand", type text}, {"Sous-Rayon", type text}, {"Kit/Standard", type text}, {"Promotional Flag", type text}, {"Sales Organization", Int64.Type}, {"Secteur", type text}, {"Customer Level 1", Int64.Type}, {"_2", type text}, {"_3", type text}, {"Calendar year / week", type text}, {"48.2018", type text}, {"49.2018", type text}, {"50.2018", type text}, {"51.2018", type text}, {"52.2018", type text}, {"01.2019", type text}, {"02.2019", type text}, {"03.2019", type text}, {"04.2019", type text}, {"05.2019", type text}, {"06.2019", type text}, {"07.2019", type text}, {"08.2019", type text}, {"09.2019", type text}, {"10.2019", type text}, {"11.2019", type text}, {"12.2019", type text}, {"13.2019", type text}, {"14.2019", type text}, {"15.2019", type text}, {"16.2019", type text}, {"17.2019", type text}, {"18.2019", type text}, {"19.2019", type text}, {"20.2019", type text}, {"21.2019", type text}, {"22.2019", type text}, {"23.2019", type text}, {"24.2019", type text}, {"25.2019", type text}, {"26.2019", type text}, {"27.2019", type text}, {"28.2019", type text}, {"29.2019", type text}, {"30.2019", type text}, {"31.2019", type text}, {"32.2019", type text}, {"33.2019", type text}, {"34.2019", type text}, {"35.2019", type text}, {"36.2019", type text}, {"37.2019", type text}, {"38.2019", type text}, {"39.2019", type text}, {"40.2019", type text}, {"41.2019", type text}, {"42.2019", type text}, {"43.2019", type text}, {"44.2019", type text}, {"45.2019", type text}, {"46.2019", type text}, {"47.2019", type text}, {"48.2019", type text}, {"49.2019", type text}, {"50.2019", type text}, {"51.2019", type text}, {"52.2019", type text}, {"01.2020", type text}, {"02.2020", type text}, {"03.2020", type text}, {"04.2020", type text}, {"05.2020", type text}, {"06.2020", type text}, {"07.2020", type text}, {"08.2020", type text}, {"09.2020", type text}, {"10.2020", type text}, {"11.2020", type text}, {"12.2020", type text}, {"13.2020", type text}, {"14.2020", type text}, {"15.2020", type text}, {"16.2020", type text}, {"17.2020", type text}, {"18.2020", type text}, {"19.2020", type text}, {"20.2020", type text}, {"21.2020", type text}, {"22.2020", type text}, {"23.2020", type text}, {"24.2020", type text}, {"25.2020", type text}, {"26.2020", type text}, {"27.2020", type text}, {"28.2020", type text}, {"29.2020", type text}, {"30.2020", type text}, {"31.2020", type text}, {"32.2020", type text}, {"33.2020", type text}, {"34.2020", type text}, {"35.2020", type text}, {"36.2020", type text}, {"37.2020", type text}, {"38.2020", type text}, {"39.2020", type text}, {"40.2020", type text}, {"41.2020", type text}, {"42.2020", type text}, {"43.2020", type text}, {"44.2020", type text}, {"45.2020", type text}, {"46.2020", type text}, {"47.2020", type text}, {"_4", type any}}),
#"Erreurs supprimées" = Table.RemoveRowsWithErrors(#"Type modifié1")
in
#"Erreurs supprimées"
My problem is the following : on this files, every week, some columns names are changing according to the date. I've been looking on several forums to make this first line as a header dynamic, and someone suggested to delete the step "Changed Type" (that is being created by itself after the step "Promoting Headers") in the applied steps in the editor. I did it, it work well to update this first line's names of the target columns, unfortunately, removing errors didn't do anything, I still have the second first line repeated in my file....
So as a beginner, I'm being stuck there... first I don't understand why the step "Changed Type" is created automatically after "Promoted Headers", and if there's another way than removing errors to make sure my header isn't repeated in my table because this step doesn't work if I'm deleting the step "Changed Type".... Thanks for your precious help !
Victoire
