Forum Discussion

RamblingFire's avatar
RamblingFire
Regular Visitor
2 years ago
Solved

Csv into table

Hi, 

 

Newbie here looking for some help. 

 

I have a csv file for example with 2 sections, top let's call compliant and second section called non compliant. Each can be of any number of rows

 

Header row 

A,b,c

Header row

X,y,z

 

I'd like to transform into:

Date,Compliant,a,b,c

Date,Not Compliant,x,y,z

 

File arrives via email, power automate saves to one drive and aiming to load into power bi or dataflow. New data arrives via email to be added into table.

 

I'm stuck trying to find how to tag the rows by their section, ie tag row before header pattern and after header pattern. 

 

Any help would be great! 

Need Help
  • AlienSx's avatar
    AlienSx
    2 years ago

    hello, RamblingFire one of possible ways to do this is to import csv into PQ, promote headers, find a position of non-compliant part using headers (Table.PositionOf), split your table (Split.At), add compliant / not compliant status column and combine tables back together. 

    let
    csv_import = #table(
        {"h1", "h2", "h3"}, 
        {{"a", "b", "c"}, {"d", "e", "f"},
        {"h1", "h2", "h3"}, 
        {"a", "b", "c"}, {"a", "b", "c"}}
    ),
    cols = List.Buffer(Table.ColumnNames(csv_import)),
    split = Table.SplitAt(
        csv_import,
        Table.PositionOf(
            csv_import, 
            Record.FromList(cols, cols)
        )
    ),
    result = 
        Table.AddColumn(split{0}, "Status", each "Compliant") & 
        Table.AddColumn(Table.PromoteHeaders(split{1}), "Status", each "Not Compliant")
in
    result

5 Replies

  • AlienSx's avatar
    AlienSx
    Super User
    2 years ago

    hello, RamblingFire one of possible ways to do this is to import csv into PQ, promote headers, find a position of non-compliant part using headers (Table.PositionOf), split your table (Split.At), add compliant / not compliant status column and combine tables back together. 

    let
    csv_import = #table(
        {"h1", "h2", "h3"}, 
        {{"a", "b", "c"}, {"d", "e", "f"},
        {"h1", "h2", "h3"}, 
        {"a", "b", "c"}, {"a", "b", "c"}}
    ),
    cols = List.Buffer(Table.ColumnNames(csv_import)),
    split = Table.SplitAt(
        csv_import,
        Table.PositionOf(
            csv_import, 
            Record.FromList(cols, cols)
        )
    ),
    result = 
        Table.AddColumn(split{0}, "Status", each "Compliant") & 
        Table.AddColumn(Table.PromoteHeaders(split{1}), "Status", each "Not Compliant")
in
    result