Get Informations from a table

[GabsMello]

Hello Guys,

I am trying to get informations from a table and put this informations in another table. I think that in the picture bellow you can understand better.

So, I want that Customer (Table 1) appears in another table (2 or 3). When I have 2 Pre-Requisites in the same row means AND, and when the number have 1+ rows means OR.

I tried to merge Table 1 and Table 2 using the product, but I don't know why Power BI uses the first pre requiste that the system find, and the others nothing happens. Example: Product 4 only merge the pre requisite 1, and not 0.

Thank u guysss,
GabsMello

[v-yueyunzh-msft · Accepted solution]

Hi , @GabsMello 

According to your description, you want to get the 'Table 1' data to the 'Table 2' data to generate a table 'Table 3'. Right?

And in one row , use the "AND" logic and for more rows, use the "OR" logic.

I think it can be realized in Power Query Editor, my test data is the same as yours.

We can put this M code in 'Table 2' "Advanced Editor":

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQKhWJ1oJSMgwxDGMYZyjMA8E2QpEMcAxjGFcgyVYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, #"Pre-Requistile 1" = _t, #"Pre-Requistile 2" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", Int64.Type}, {"Pre-Requistile 1", Int64.Type}, {"Pre-Requistile 2", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", (x)=>if x[#"Pre-Requistile 1"]<>null and  x[#"Pre-Requistile 2"]<>null then  Table.SelectRows(Table.Group(Table.SelectRows(#"Table 1",(y)=> y[Product]=x[#"Pre-Requistile 1"] or y[Product]=x[#"Pre-Requistile 2"]) , "Customer",{"test",(z)=>Table.RowCount(z)  }   ),(c)=> c[test]=2 )  else if x[#"Pre-Requistile 1"]=null and  x[#"Pre-Requistile 2"]=null then null else if x[#"Pre-Requistile 1"]<>null then Table.SelectRows(#"Table 1",(y)=> y[Product]=x[#"Pre-Requistile 1"] ) else Table.SelectRows(#"Table 1",(y)=> y[Product]=x[#"Pre-Requistile 2"] )

  ),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Pre-Requistile 1", "Pre-Requistile 2"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Customer"}, {"Customer"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Custom", each ([Customer] <> null))
in
    #"Filtered Rows"

Then we can meet your need:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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

[ryan_mayu]

@GabsMello 

what's the expected output based on your sample data?