cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cathoms
Resolver I
Resolver I

Remove subset of rows

Hello. I am trying to automate a task that currently invovles a lot of copy and paste in Excel with the end goal of a dataset for Power BI. Currently, we run reports from a website that produces an excel sheet, then copy needed values into another sheet. There are about two dozen of these reports. What I am working on is building a data model with all of the sheets, with the intent of just downloading the Excel files into a single folder with updated data each month. So, I want to avoid modifying the excel sheets up front as manipulating each separate file each month would sort of defeat the point of this project.

 

The nature of the excel files requires some substantial transformation. I've done this but now realize I need some additional columns in order to create lookup tables and relationships. I'm okay with the Power Query Editor GUI but have only a limited, very basic grasp of the M Language. Anywho, my current challenge is to remove a specific set of rows.

 

Referencing the image below, I want to remove all of the top rows down to 37, except row 18, "SITE". Is there a way to do that?

 

cathoms_0-1616766431928.png

 

1 ACCEPTED SOLUTION

Hello @cathoms 

 

I combined now both data into one table. The main table is basically created by checking the content of two columns.. basically creating the table from backwards until criteria a matching. Then extract with Table.SelectRows the value of your site and add it as new column. Here the complete code

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtKBoVidaCVn32BUAVRecGpRWWZyKpCblllUXIJTHZouzxBXIDsRCIjU4ZaZU5JahE8FLncVpybn56UAGYWlqcUlmfl5ILHSorLUSrBCQ1R9RqhcY1SuCSrXFJVrRrzznB09AvCGKy7flGRkFiF7BiRWlphTmgpW5wjkgTwEcqehIVwI5CmQW42M4EIgj4Hca2ysFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Colonna3 = _t, Colonna4 = _t]),
    Last = Table.RemoveFirstN(Source, each ([Column1]<>"Service" or [Column2]<>"third")),
    #"Promoted Headers" = Table.PromoteHeaders(Last, [PromoteAllScalars=true]),
    GetSite = Table.SelectRows(Source, each [Column1]= "SITE"){0}[Column2],
    AddSiteToTable = Table.AddColumn
    (
        #"Promoted Headers",
        "Site", 
        each GetSite
    )
    
in
    AddSiteToTable

and this is the output

Jimmy801_0-1616830161942.png

 

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

View solution in original post

4 REPLIES 4
Jimmy801
Super User III
Super User III

Hello @cathoms 

 

this question I can only answer if I know how your data is strucutred. Is its always that the first 37 rows have to be canceled (then use Table.RemoveFirst(yourTable, 37)) if not, you need a dynamic solution like this where i check the content of two columns to identify how many rows to remove

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJRUorViVZy9g2GMWF0cGpRWWZyKpCblllUXIIiB1fjGeIKZCcCAVZ5t8ycktQidFFMG4pTk/PzUsCChjBZIxjDGMYwgTFMYQwzXEY7O3oEYPgI096SjMwiiLWOMEk0RiwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    Last = Table.RemoveFirstN(Source, each ([Column1]<>"Service" or [Column2]<>"third")),
    #"Promoted Headers" = Table.PromoteHeaders(Last, [PromoteAllScalars=true])
    
in
    #"Promoted Headers"

I also don't see to much sense in adding to a table that has headers a row that has nothing to do with that. What you can do to create 2 outputs within a list. One is your service table, one is a record of a specific row. Here an example

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJRUorViVZy9g2GMWF0cGpRWWZyKpCblllUXIIiB1fjGeIKZCcCAVZ5t8ycktQidFFMG4pTk/PzUsCChjBZIxjDGMYwgTFMYQwzXEY7O3oEYPgI096SjMwiiLWOMEk0RiwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    Last = Table.RemoveFirstN(Source, each ([Column1]<>"Service" or [Column2]<>"third")),
    #"Promoted Headers" = Table.PromoteHeaders(Last, [PromoteAllScalars=true]),
    FourthRow = Source{3},
    CreateOutput = {#"Promoted Headers", FourthRow}
    
in
    CreateOutput

Jimmy801_0-1616769396293.png


If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

 

Thanks so much! To answer your questions and provide more detail, this particular report always has the same structure. Others are structured slightly differently with different number of useless top rows. At first I was just removing the top 37 rows but then my final query/table doesn't have the site name. What I am aiming for is a column with the site name (along with columns for service, date, etc.) that I can use to create and relate to a lookup table.

 

That is, I'm trying to go from the image in my original post to the following, except with the additional "site" column. Incidentally, I also need to add a colums for "unit", but I think I have a workaround for that problem.

 

cathoms_0-1616775260534.png

 

Hello @cathoms 

 

I combined now both data into one table. The main table is basically created by checking the content of two columns.. basically creating the table from backwards until criteria a matching. Then extract with Table.SelectRows the value of your site and add it as new column. Here the complete code

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtKBoVidaCVn32BUAVRecGpRWWZyKpCblllUXIJTHZouzxBXIDsRCIjU4ZaZU5JahE8FLncVpybn56UAGYWlqcUlmfl5ILHSorLUSrBCQ1R9RqhcY1SuCSrXFJVrRrzznB09AvCGKy7flGRkFiF7BiRWlphTmgpW5wjkgTwEcqehIVwI5CmQW42M4EIgj4Hca2ysFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Colonna3 = _t, Colonna4 = _t]),
    Last = Table.RemoveFirstN(Source, each ([Column1]<>"Service" or [Column2]<>"third")),
    #"Promoted Headers" = Table.PromoteHeaders(Last, [PromoteAllScalars=true]),
    GetSite = Table.SelectRows(Source, each [Column1]= "SITE"){0}[Column2],
    AddSiteToTable = Table.AddColumn
    (
        #"Promoted Headers",
        "Site", 
        each GetSite
    )
    
in
    AddSiteToTable

and this is the output

Jimmy801_0-1616830161942.png

 

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

View solution in original post

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtKBoVidaCVn32BUAVRecGpRWWZyKpCblllUXIJTHZouzxBXIDsRCIjU4ZaZU5JahE8FLncVpybn56UAGYWlqcUlmfl5ILHSorLUSrBCQ1R9RqhcY1SuCSrXFJVrRrzznB09AvCGKy7flGRkFiF7BiRWlphTmgpW5wjkgTwEcqehIVwI5CmQW42M4EIgj4Hca2ysFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Colonna3 = _t, Colonna4 = _t]),
    Last = Table.RemoveFirstN(Source, each ([Column1]<>"Service" or [Colonna4]<>"value")),
    site=Source{[Column1="SITE"]}[Column2],
    #"Promoted Headers" = Table.PromoteHeaders(Last, [PromoteAllScalars=true]),
    #"Aggiunta colonna personalizzata" = Table.AddColumn(#"Promoted Headers", "SITE", each site)
in
    #"Aggiunta colonna personalizzata"

 

 

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Urdu Hindi D365 Bootcamp 768x460.png

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors
Top Kudoed Authors