Forum Discussion
Remove subset of rows
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 AddSiteToTableand this is the output
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
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
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.
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 AddSiteToTableand this is the output
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
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"
