Reducing table size by filtering

[Anonymous]

Hi,

I'm having a table, let's call it Table A. How can I reduce the table to only rows where ID-Number is 1, so it has only to rows (the first and the last one) left? Important: the table is already filled with extra columns I added after the import of the data.

ID-Number	Product A	Product B	Product C	Product D
1	1,5	2	7	9
0	5	8	3	1
-1	2	3	1	8
1	7	5	6	2

Any ideas which way I could get the desired result? 

Thanks in advance,

Felix

[v-shex-msft]

Hi @Anonymous,

Did these replies help for your scenario? If this is a case, you can consider accepting the suitable one to help other users who faced the same requirement to find it quickly. If not, please feel free post here with detailed information,
Regards,
Xiaoxin Sheng

[AlB · Accepted solution]

@Anonymous 

@mahoneypat  has already provided a solution and full explanation. Here's another one in case it is easier to follow (although I am not sure it will on the first custom column). You'll have however to build some basic PQ knowledge on your own .It first creates a custom column with a list of the ID-Number values for the  Contract number in that row. Then it creates another custom column that checks if those values contain  1 and -1. Finally it filters out the Contracts with 1 and -1:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("NYzBDQAhCAR74Y3JoadgLYT+27hFuccQyO7gTtIHMUnCE7MDBZuCndQ29gdkZuC2M7tmk5L+N3bCd666teR1ehEf", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Contract-number" = _t, #"ID-Number" = _t, #"Product A" = _t, #"Product B" = _t, #"Product C" = _t, #"Product D" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Contract-number", Int64.Type}, {"ID-Number", Int64.Type}, {"Product A", Int64.Type}, {"Product B", Int64.Type}, {"Product C", Int64.Type}, {"Product D", Int64.Type}}),

    #"Added Custom" = Table.AddColumn(#"Changed Type", "IDNumbers in contract", each Table.SelectRows(#"Changed Type", (inner)=>(inner)[#"Contract-number"] = [#"Contract-number"])[#"ID-Number"]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Has 1 and -1", each List.Contains([IDNumbers in contract],1) and List.Contains([IDNumbers in contract],-1)),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([#"Has 1 and -1"] = false))
in
    #"Filtered Rows"

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[amitchandak]

@Anonymous , source and target is not clear

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Anonymous]

@amitchandak: Maybe the tables below make it clearer for you? I'm in the "data-view" of PowerBI and I want to delete the two rows in the middle by reffering to the ID-Number 0 and -1. Which options do I have to receive the modified table?

Original tabkle

ID-Number	Product A	Product B	Product C	Product D
1	1,5	2	7	9
0	5	8	3	1
-1	2	3	1	8
1	7	5	6	2

Modified table:

ID-Number	Product A	Product B	Product C	Product D
1	1,5	2	7	9
1	7	5	6	2

[mahoneypat]

In the query editor, just add a step to filter your ID Number column to values >= 1.

Regards,

Pat

[Anonymous]

 @mahoneypat  Thanks for your reply!

Can you tell me how its done? Which formula to use and how would it look like?

And one further question which is a little bit more complex. I want to remove every pair of a contract number which has the ID-Number 1 and -1. However, a single contract number with -1 should still be in the table. Only for the case of a contract number having both arguments (1 and -1), the rows shall be deleted. So for the example I want to remove line 1 and 3.  How does the formula look like? 

Contract-number	ID-Number	Product A	Product B	Product C	Product D
123	1	1,5	2	7	9
789	0	5	8	3	1
123	-1	2	3	1	8
456	1	7	5	6	2

Sorry for asking in detail how it's done, but actually I've got only little experience so far.

Regards,

Felix

[mahoneypat]

Here is one way to do that in the query editor.  To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.  Note that I recommend you also unpivot your product columns to set up your data for easier/better analysis visualization.  You can delete that step from the query if you want.  All the steps can be done from buttons in the ribbon, expect the #"Added Custom" which has some custom M code, but hopefully you can replicate on your actual query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("NYzBDQAhCAR74Y3JoadgLYT+27hFuccQyO7gTtIHMUnCE7MDBZuCndQ29gdkZuC2M7tmk5L+N3bCd666teR1ehEf", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Contract-number" = _t, #"ID-Number" = _t, #"Product A" = _t, #"Product B" = _t, #"Product C" = _t, #"Product D" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Contract-number", Int64.Type}, {"ID-Number", Int64.Type}, {"Product A", Int64.Type}, {"Product B", Int64.Type}, {"Product C", Int64.Type}, {"Product D", Int64.Type}}),
    #"Grouped Rows1" = Table.Group(#"Changed Type", {"Contract-number"}, {{"AllRows", each _, type table [#"Contract-number"=nullable number, #"ID-Number"=nullable number, Product A=nullable number, Product B=nullable number, Product C=nullable number, Product D=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows1", "FilterOut", each if List.ContainsAll([AllRows][#"ID-Number"], {-1, 1}) then "Y" else "N"),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([FilterOut] = "N")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"FilterOut"}),
    #"Expanded AllRows" = Table.ExpandTableColumn(#"Removed Columns", "AllRows", {"ID-Number", "Product A", "Product B", "Product C", "Product D"}, {"ID-Number", "Product A", "Product B", "Product C", "Product D"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Expanded AllRows", {"Contract-number", "ID-Number"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Project"}})

in
    #"Renamed Columns"

Regards,

Pat

[Anonymous]

@mahoneypat Appreciate your answer and support, but to be honest the explanation and code leaves me more confused than before. Would explain it based on my little example table? That would be awesome.

Regards,

Felix

[mahoneypat]

The M code provided is just an example of how to transform your example data.  Note that it removes those rows from the data.  If you want only to temporarily exclude them for a given calculation, that can be done with a DAX expression.

If you look at your existing query in the query editor (hit Transform Data in the ribbon), and click on Advanced Editor in the ribbon, you will see similar code.  You need to create a blank query (click on New Source and Blank Query), open its Advanced Editor and replace the default code there with what I provided.  You will then see steps to transform your data.  If that is the result you want, you then need to replicate those steps on your original query.

Regards,

Pat