Share via

Power Query: (Data from Folder) New CSV with added columns replacing last columns from other csv in folder.

Anonymous
2021-07-02T17:47:13+00:00

Hi,

I have this request in Power Query to extract data from a folder on OneDrive. Let's call this request "Data". The folder originally contained csv files with 29 columns with the last two being "y" and "z". The new CSV files related to my work contain 2 additional columns (+ same 29 from previous csv) called "w" and "x" that were placed right before y and z; Increasing the total columns to 31 ending with w,x,y,z in that order. I thought that adding the new CSV files to the folder would automatically add the new columns to the request and give null values to rows from previous CSV files without w and x. However, Power Query's built-in source code deleted columns y and z from the request to add w and x; it seems like the code returns a fixed number of columns based on the original request "Data" and delete the last ones outside of its range. I even tried to merge csv files on my own and only put that one in the folder, but without success. I looked on this forum among others, but found no answer to this issue. Please find the code below:

Source = Folder.Files("C:\file-location"), 

#"Autres colonnes supprimées" = Table.SelectColumns(Source,{"Content"}), 

#"Fichiers masqués filtrés1" = Table.SelectRows(#"Autres colonnes supprimées", each [Attributes]?[Hidden]? <> true), 

#"Appeler une fonction personnalisée1" = Table.AddColumn(#"Fichiers masqués filtrés1", "Transformer le fichier", each #"Transformer le fichier"([Content])), 

#"Autres colonnes supprimées1" = Table.SelectColumns(#"Appeler une fonction personnalisée1", {"Transformer le fichier"}), 

#"Colonne de tables développée1" = Table.ExpandTableColumn(#"Autres colonnes supprimées1", "Transformer le fichier", Table.ColumnNames(#"Transformer le fichier"(#"Exemple de fichier")))

Please let me know if you need additional information to help me resolve this issue. This is confidential work, so I can't openly share the file or column names. Cheers

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous
2021-07-02T19:35:16+00:00

Hey Ron, I actually figured out the problem on my own a few minutes ago. When getting data from a folder, the source code in advanced editor won't typically feature this line:

Source = Csv.Document(Paramètre2,[Delimiter=",", Encoding=1252, Columns=35, QuoteStyle=QuoteStyle.None])

To resolve my issue, I had to delete the limit number of columns returned by powerquery ->

Source = Csv.Document(Paramètre2,[Delimiter=",", Encoding=1252, QuoteStyle=QuoteStyle.None])

You can find this line of code in the advanced editor under "Transform File"

Cheers

Was this answer helpful?

0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2021-07-02T19:18:26+00:00

    Perhaps you could create some sample files that illustrate the problem.

    Was this answer helpful?

    0 comments