none
Always Rename First Column To Static Text String RRS feed

  • Question

  • Hi guys,

    scenario.

    I import from multiple CSVs via Csv.Document(File.Contents(....)). The reason why I do this is that I want to keep source filename within the query.

    The trouble is when file order in the source folder changes. On the first Promote.Headers, the name of the first file gets pushed to Header row, the trouble is that this filename is variable, thus the header is variable and it causes issues later on in the query.

    I would like a piece of code which would always rename the first column to "Source" regardless of the current header value.

    Is something like this possible?

    DZ

    Friday, November 11, 2016 2:57 PM

Answers

  • Hi!

    Didn't truly understand your scenario, but concerning your question, here it is:

    = Table.RenameColumns(Table_name,{Table.ColumnNames(Table_name){0},"Source"})


    Maxim Zelensky Excel Inside

    Friday, November 11, 2016 3:49 PM

All replies

  • Hi!

    Didn't truly understand your scenario, but concerning your question, here it is:

    = Table.RenameColumns(Table_name,{Table.ColumnNames(Table_name){0},"Source"})


    Maxim Zelensky Excel Inside

    Friday, November 11, 2016 3:49 PM
  • awesome, thank you!
    Friday, November 11, 2016 3:54 PM
  • Hi Daniel,

    Could you let me know the difference between what you're asking for and the solution provided in this thread?

    https://social.technet.microsoft.com/Forums/en-US/4015b8a3-a7c5-4dc3-a7fd-1cb43a8c5779/how-to-reorder-columns-first-then-use-binarycombine-to-combine-files?forum=powerquery

    Perhaps I can modify that solution to fit your needs.

    Friday, November 11, 2016 4:02 PM
  • Hi Colin,

    the solution you are providing is different use case (btw, I am using your code sometimes).

    My thing is simpler in this case. I am not reordering columns. I am just renaming.

    My code is this:

    let
        Source = Folder.Files(Get_File("YYZr")),
        #"Removed Other Columns" = Table.SelectColumns(Source,{"Name", "Folder Path"}),
        #"Added Custom" = Table.AddColumn(#"Removed Other Columns", "File", each Csv.Document(File.Contents([Folder Path]&[Name]))), .... this tricks keeps file name opposed to Binary Combine, which drops all "attribute columns".
        #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Folder Path"}),
        #"Expanded File" = Table.ExpandTableColumn(#"Removed Columns", "File", {"Column1", "Column2", "Column3", "Column4", "Column5"}, {"Column1", "Column2", "Column3", "Column4", "Column5"}),
        #"Promoted Headers" = Table.PromoteHeaders(#"Expanded File"),
        #"Filtered Rows" = Table.SelectRows(#"Promoted Headers", each ([Date.Date] <> "Date.Date" and [Date.Date] <> "Total")),
        #"XX - First Col As Source" = Table.RenameColumns(#"Filtered Rows",{Table.ColumnNames(#"Filtered Rows"){0},"Source"}),

    I hope it makes more sense now.

    DZ


    Friday, November 11, 2016 4:06 PM