none
Power Query : How to repeat a CSV header line in an aditional column at each row of data

    Question

  • Hello,

    I've got to recursively import a full folder of bank account history in CSV format.

    Those are generated automatically without any control on their structure or overlapping entries (because of fixed date range) .

    Thanks to Power querry I should be able to combine all the data in one excel Pivot table removing all duplicate rows... but I'm stuck on one thing :

    CSV files are structured like this :

    Line 1 = "Account Nr";xxxxxxxx;"acount name";xxxxxxxx

    Line 2 = 15 columns headers separated by semicolons

    Line 2 to 1000 = rows of data separated by semicolons

    Line 1001 = "Account Nr";xxxxxxxx;"acount name";xxxxxxxx

    Line 1002 = columns headers separated by semicolons

    Line 1003 to 2000 = rows of data separated by semicolons

    etc.

    so I would like to automatically have the Account Nr & name added as prefix to all rows of data until next "Account NR" entry.

    It is so easy to do in each file in excel by copying above cell for each row until "Account NR" is present and then get the new value to repeat.

    As I haven't find a way to conditionally repeat the above cell value in Power Query, i'm asking your help

    ;o)


    • Edited by opesch Friday, February 14, 2014 6:28 PM
    • Moved by Adam Wilson (Microsoft) Thursday, February 20, 2014 5:45 PM Moving to the Power Query forum
    Friday, February 14, 2014 6:25 PM

Answers

  • Hello,

    maybe it is too late but you can try this

    Column2 contains Account number and Column 4 contains Account Name and Column1 contains sometimes the word Account

    InsertedCustom = Table.AddColumn(Source, "Custom", each [Column2]&"-"&[Column4]),

    InsertedCustom1 = Table.AddColumn(InsertedCustom, "Custom.1", each if Text.Start([Column1],3)="acc" then [Custom] else null),

    Remplir1 = Table.FillDown(InsertedCustom1,{"Custom.1"}),

    It works for me

    Jean-Pierre Girardot

    Tuesday, March 18, 2014 7:19 PM

All replies

  • As I haven't already got an answer yet, I post here my last researches :

    I've found a way to keep corresponding filename & path info in front of each rows of data.... This may be a path to the solution ?

    but then the Csv data rows comes as "error" when using "Csv.Document([Content])" for a new row and expanding the table... I was already able to overcome this by rightclicking on file at the Combine.Binaries step and setting it to "Text" but it doens't seems possible on the expanded table....

    So I tried to combine both type of steps together... but It still does not work !

    here is the not working code :

    let
        Source = Folder.Files("C:\Users\opesch\Google Drive\DOOCS\#_Doocs_#\maman\Comptes\Compte Record"),
        FilteredRows = Table.SelectRows(Source, each ([Extension] = ".csv")),
        RemovedColumns = Table.RemoveColumns(FilteredRows,{"Date accessed", "Date created", "Attributes"}),
        InsertedCustom = Table.AddColumn(RemovedColumns, "Custom", each Csv.Document(Table.FromColumns({Lines.FromBinary([Custom])})))
    in
        InsertedCustom

    while this code herunder was fully working.... but for combined data rows only, without any fixed heading value :

    let
        Source = Folder.Files("xxxxxxx"),
        FilteredRows = Table.SelectRows(Source, each ([Extension] = ".csv")),
        RemovedColumns = Table.RemoveColumns(FilteredRows,{"Date accessed", "Date created", "Attributes"}),
        CombinedBinaries = Binary.Combine(RemovedColumns[Content]),
        ImportedText = Table.FromColumns({Lines.FromBinary(CombinedBinaries)})
    in
        ImportedText

    Do you have an idea on where I made a mistake ?

    Thanks


    Saturday, February 15, 2014 5:47 PM
  • Hello,

    maybe it is too late but you can try this

    Column2 contains Account number and Column 4 contains Account Name and Column1 contains sometimes the word Account

    InsertedCustom = Table.AddColumn(Source, "Custom", each [Column2]&"-"&[Column4]),

    InsertedCustom1 = Table.AddColumn(InsertedCustom, "Custom.1", each if Text.Start([Column1],3)="acc" then [Custom] else null),

    Remplir1 = Table.FillDown(InsertedCustom1,{"Custom.1"}),

    It works for me

    Jean-Pierre Girardot

    Tuesday, March 18, 2014 7:19 PM