Forum Discussion

RamblingFire's avatar
RamblingFire
Regular Visitor
2 years ago
Solved

Csv help

Hi,    I have a csv file such as: Header1, header2, header3 A, b, c D, e, f Report generated on 19/05/2024   I'd like to transform to Date, Header1, header2, header3 19/05/2024,a,b,c 19/05...
Need Help
  • AlienSx's avatar
    AlienSx
    2 years ago

    hello, RamblingFire something else: 

    let
    Source = Csv.Document(File.Contents("full_path_to_your_file"),[Delimiter=",", Columns=3, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    prom = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    dt = Date.FromText(
        Text.AfterDelimiter(Table.Last(prom)[Header1], "Report generated on "),
        [Format = "dd/MM/yyyy"]
    ),
    z = Table.AddColumn(Table.RemoveLastN(prom, 1), "Date", each dt)
in
    z
AlienSx's avatar
AlienSx
Super User
2 years ago

hello, RamblingFire something else: 

let
    Source = Csv.Document(File.Contents("full_path_to_your_file"),[Delimiter=",", Columns=3, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    prom = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    dt = Date.FromText(
        Text.AfterDelimiter(Table.Last(prom)[Header1], "Report generated on "),
        [Format = "dd/MM/yyyy"]
    ),
    z = Table.AddColumn(Table.RemoveLastN(prom, 1), "Date", each dt)
in
    z
RamblingFire's avatar
RamblingFire
Regular Visitor
2 years ago

Thank you so much!