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/2024,d,e,f

 

Can some one help me with the table.split m function or suggest something else?

 

Thanks 

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

2 Replies

  • 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