Csv into table

[RamblingFire]

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! 

[AlienSx · Accepted solution]

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

[RamblingFire]

Hi AlienSx, 

Thanks so much for this, it looks exactly what I need. Can you share or provide me with a pointer where I can enter the above script? 

[AlienSx]

@RamblingFire watch this

[RamblingFire]

Thank you, what stands out to me is the fact that data is in the query, how would this apply to a second file with other data? 

[RamblingFire]

Ahha I think I get it, the split function uses the column headers irrelevant to where they appear. The data is in the query for example and I could just reference the table name as per 2nd example in video