Removing Rows - http://community.powerbi.com/t5/Desktop/Removing-rows-in-Power-Bi/m-p/48104#M19015

[gpiero]

I am trying to removing rows using parameters as indicated in the post above.

I have created the parameter but I did not understand how to delete the rows that match with parameter.

I can filter according to parameter but I can't delete.

Could you help me please?

Thanks

[Greg_Deckler]

I may not be understanding correctly, but you would need to use the parameter to filter in the query so that the data never makes it into the model. Are you trying to import everything and then delete it from the model? Or are you trying to delete it from the source?

[gpiero]

@Greg_Deckler

You are right since you do not know the background.

I am importing big data from ERP. The transaction that generates repeat on each page the column header.

Then shaping data, I need to remove all rows that contain the string that correspond to the column header.

[Greg_Deckler]

Is there just one source and one format for the data being imported? You could essentially just set a filter on one of the columns to filter out all "Codice materiale" values in the "Codice materiale" column.

[gpiero]

@Greg_Deckler

Do you mean in this way?

#"Filtered Rows" = Table.SelectRows(#"Removed Columns", each Text.Contains([#" Materiale "], "Materiale"))

It aggregates all rows togheter and then I can remove duplicate and remove tha last row that is not duplicated.

How to deactivate the filter at the end?

#"Filtered Rows" = Table.SelectRows(#"Removed Columns", each Text.Contains([#" Materiale "], "Materiale"))

[Greg_Deckler]

I guess I was thinking like this:

    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([ Materiale] <> "Materiale"))

[gpiero]

@Greg_Deckler

I think I misuderstood your suggestion.

How this code will allow to delete all rows that match the condition?

[Greg_Deckler · Accepted solution]

It filters them in the query so that those rows are never imported into the data model so they are essentially "deleted" as part of the data load. This will not remove them from the source file but the data model will not have those rows in it. This is just a step in the query, you would have to return the rows from that line of the query. A full example query would be:

let
    Source = Csv.Document(File.Contents("C:\temp\powerbi\departments.csv"),[Delimiter=",", Columns=3, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Department", Int64.Type}, {"Name", type text}, {"DeptType", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Name] <> "Dept3"))
in
    #"Filtered Rows"

[gpiero]

@Greg_Deckler

Thank you very much.

Now it works.

At first tentative I selected the field but i did not realize that in the formula PBI wrote

Table.SelectRows(#"Removed Blank Rows1", each ([#" Materiale "] = " Materiale ")),

instead of

Table.SelectRows(#"Removed Blank Rows1", each ([#" Materiale "] <> " Materiale ")),

let
    Source = Csv.Document(File.Contents("D:\OneDrive - x\Lavori\01.20_-_POWER BI DATASOURCE\WH Internal Activities\ZMB51_0_10000.TXT"),[Delimiter="|", Columns=20, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Change Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type text}, {"Column15", type text}, {"Column16", type text}, {"Column17", type text}, {"Column18", type text}, {"Column19", type text}, {"Column20", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Change Type",{"Column1"}),
    #"Removed Blank Rows" = Table.SelectRows(#"Removed Columns", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Blank Rows"),
    #"Replaced Value" = Table.ReplaceValue(#"Promoted Headers","--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------","",Replacer.ReplaceText,{" Materiale "}),
    #"Removed Blank Rows1" = Table.SelectRows(#"Replaced Value", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
    #"Filtered Rows" = Table.SelectRows(#"Removed Blank Rows1", each ([#" Materiale "] <> " Materiale ")),
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{""})
in
    #"Removed Columns1"

Thanks again for your precious help

[Greg_Deckler]

No problem, glad you got it working!