none
Filename in a Column RRS feed

  • Question

  • Hi, 

    I have multiple CSV files imported 'as folder' in power query. When I see the name imported file in the second column

    When I click on 'Combine Binaries button it expands all contents of each CSV but does not display the file name.

    I wonder if I can add a column and enter a formula that brings the file name for each line. What formula is?

    Saturday, June 18, 2016 5:15 PM

Answers

  • In the Advanced Editor, change the importedCSV step to:

    ImportedCSV = Table.AddColumn(<PreviousTableStepName>, "Custom", each Csv.Document(CombinedBinaries,[Delimiter=",", QuoteStyle = QuoteStyle.None, Encoding=1252])),

    <PreviousTableStepName> is the step before CombinedBinaries.

    The expression following "each" is the same as the one in the original ImportedCSV step, in the event that the example above does not match the expression in your case.

    Saturday, June 18, 2016 8:24 PM

All replies

  • In the Advanced Editor, change the importedCSV step to:

    ImportedCSV = Table.AddColumn(<PreviousTableStepName>, "Custom", each Csv.Document(CombinedBinaries,[Delimiter=",", QuoteStyle = QuoteStyle.None, Encoding=1252])),

    <PreviousTableStepName> is the step before CombinedBinaries.

    The expression following "each" is the same as the one in the original ImportedCSV step, in the event that the example above does not match the expression in your case.

    Saturday, June 18, 2016 8:24 PM
  • Actually, your ImportedCSV expression will be different from the given example, which is customized for importing Power Query log files (except that for log files, I use a "{" delimiter instead of ",").
    Sunday, June 19, 2016 5:34 AM
  • Hello

    I have been struggling with adding this step and not sure what I am doing wrong.  Here is my code:

    let
        Source = Folder.Files("C:\Users\amp dc 2017"),
        #"Combined Binaries" = Binary.Combine(Source[Content]),
        #"Imported CSV" = Csv.Document(#"Combined Binaries",[Delimiter="#(tab)", Columns=33, Encoding=65001, QuoteStyle=QuoteStyle.None]),
        #"Promoted Headers" = Table.PromoteHeaders(#"Imported CSV"),
        #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Supplier Number", type text}, {"Supplier Name", type text}, {"Invoice Number", type text}, {"Invoice Line Number", type text}, {"Invoice Date", type text}, {"Invoice Description", type text}, {"Invoice Amount", type number}, {"Invoice Line Amount", type number}, {"Check/Payment Number", type text}, {"Check/Payment Date", type text}, {"Check/Payment Amount", type number}, {"Check/Payment Method", type text}, {"Payment Terms Description", type text}, {"Currency Code", type text}, {"Purchase Order Number", type text}, {"PO Line Item Number", type text}, {"Location Code", type text}, {"Data Source", type text}, {"GL Code", type text}, {"GL Description", type text}, {"Platform", type text}, {"Business Unit Description", type text}, {"Cost Center Code", Int64.Type}, {"Cost Center Description", type text}, {"Company Code", type text}, {"Company Description", type text}, {"Freight Line Item", type text}, {"Freight Line Amount", type text}, {"Freight Line Description", type text}, {"Incoterms", type text}, {"Intercompany Transfer", type text}, {"Location Name", type text}, {"", type text}}),
        #"Merged Queries" = Table.NestedJoin(#"Changed Type",{"Currency Code"},curconversion,{"currency"},"NewColumn",JoinKind.LeftOuter),
        #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"currency", "conversion factor"}, {"NewColumn.currency", "NewColumn.conversion factor"}),
        #"Added Custom" = Table.AddColumn(#"Expanded NewColumn", "Converted$", each [Invoice Line Amount]*[NewColumn.conversion factor]),
        #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Converted$", Currency.Type}})
    in
        #"Changed Type1

    I tried tweaking the 3rd step and then the follow steps stop working.  Here is the code I tried:

    #"Imported CSV" = Table.AddColumn(Source, "Custom",  each Csv.Document(#"Combined Binaries",[Delimiter="#(tab)", Columns=33, Encoding=65001, QuoteStyle=QuoteStyle.None])),

    any quick thoughts?

    Wednesday, March 8, 2017 4:35 PM
  • Quick thoughts:

    You need to replace 3 steps:

        #"Combined Binaries" = Binary.Combine(Source[Content]),
         #"Imported CSV" = Csv.Document(#"Combined Binaries",[Delimiter="#(tab)", Columns=33, Encoding=65001, QuoteStyle=QuoteStyle.None]),
         #"Promoted Headers" = Table.PromoteHeaders(#"Imported CSV"),



    with this step:

    #"Promoted Headers" = Table.AddColumn(Source, "Custom", each Table.PromoteHeaders(Csv.Document([Content],[Delimiter="#(tab)", Columns=33, Encoding=65001, QuoteStyle=QuoteStyle.None])))

    Pls let me know if it works - & if you want to know why ;-)


    Imke Feldmann TheBIccountant.com




    Wednesday, March 8, 2017 7:40 PM
    Moderator
  • So the modified 3rd step works? If so, what error are you getting after this step?
    Wednesday, March 8, 2017 7:49 PM