Forum Discussion

Mery1526's avatar
Mery1526
New Member
4 years ago
Solved

Can I extract rows between two text values in power query?

I am currently preparing some data to be visualized in power bi. I have a dataset that looks like the following example :

I need to extract 3 tables, one table for each state. How can I achieve this using power query?

Please note that I need to repeat the same query on multiple daily datasets that look like the picture above. However, the number of rows for each state is not stable. I might have two rows for state 2 in this dataset, but for another dataset there could be more.

I need a way that would enable me to extract the rows between state 1 and state 2, store them in a new table, and so on for the rest of the dataset.

 

Any help is much appreciated. Thanks in advance.

Need Help
Tutorial Requests
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    Hi Mery1526 ,

    According to your description, here's my solution, copy the below codes to blank queries.

    state1 table:

    let
    #"source"=Table.SelectRows(#"Sheet1",each [Index]> Table.SelectRows(#"Sheet1",(x)=>x[Column1]="state1")[Index]{0} and [Index]<Table.SelectRows(#"Sheet1",(x)=>x[Column1]="state2")[Index]{0}),
    #"Removed Columns" = Table.RemoveColumns(source,{"Index"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Columns", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID", Int64.Type}, {"City", type text}})
in
  #"Changed Type"

    Result:

    state2 table:

    let
    #"source"=Table.SelectRows(#"Sheet1",each [Index]> Table.SelectRows(#"Sheet1",(x)=>x[Column1]="state2")[Index]{0} and [Index]<Table.SelectRows(#"Sheet1",(x)=>x[Column1]="state3")[Index]{0}),
    #"Removed Columns" = Table.RemoveColumns(source,{"Index"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Columns", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID", Int64.Type}, {"City", type text}})
in
  #"Changed Type"

    Result: 

    state3 table:

    let
    #"source"=Table.SelectRows(#"Sheet1",each [Index]> Table.SelectRows(#"Sheet1",(x)=>x[Column1]="state3")[Index]{0}),
    #"Removed Columns" = Table.RemoveColumns(source,{"Index"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Columns", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID", Int64.Type}, {"City", type text}})
in
  #"Changed Type"

    Result:

    I attach my sample below for reference.

     

    Best Regards,
    Community Support Team _ kalyj

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

3 Replies

  • rohit_singh's avatar
    rohit_singh
    Icon for Solution Sage rankSolution Sage
    4 years ago

    Hi Mery1526 ,

    Please try the steps below :

    1) Load the dataset into power query

     

    2) Add a custom column for State. If column 1 contains "State" then value else null

    3) Perform a fill down operation on the new column. This will replace nulls with the value of the previous row

    4)Filter out blank rows from column 2 and promote headers


    5) Finally filter ID <> "ID". This will leave you with a usable dataset.

    Kind regards,

    Rohit


    Please mark this answer as the solution if it resolves your issue.
    Appreciate your kudos! ğŸ˜Š

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi Mery1526 ,

    According to your description, here's my solution, copy the below codes to blank queries.

    state1 table:

    let
    #"source"=Table.SelectRows(#"Sheet1",each [Index]> Table.SelectRows(#"Sheet1",(x)=>x[Column1]="state1")[Index]{0} and [Index]<Table.SelectRows(#"Sheet1",(x)=>x[Column1]="state2")[Index]{0}),
    #"Removed Columns" = Table.RemoveColumns(source,{"Index"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Columns", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID", Int64.Type}, {"City", type text}})
in
  #"Changed Type"

    Result:

    state2 table:

    let
    #"source"=Table.SelectRows(#"Sheet1",each [Index]> Table.SelectRows(#"Sheet1",(x)=>x[Column1]="state2")[Index]{0} and [Index]<Table.SelectRows(#"Sheet1",(x)=>x[Column1]="state3")[Index]{0}),
    #"Removed Columns" = Table.RemoveColumns(source,{"Index"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Columns", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID", Int64.Type}, {"City", type text}})
in
  #"Changed Type"

    Result: 

    state3 table:

    let
    #"source"=Table.SelectRows(#"Sheet1",each [Index]> Table.SelectRows(#"Sheet1",(x)=>x[Column1]="state3")[Index]{0}),
    #"Removed Columns" = Table.RemoveColumns(source,{"Index"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Columns", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID", Int64.Type}, {"City", type text}})
in
  #"Changed Type"

    Result:

    I attach my sample below for reference.

     

    Best Regards,
    Community Support Team _ kalyj

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.