Forum Discussion

RicardoLeivaG's avatar
RicardoLeivaG
Helper I
5 years ago
Solved

Ayuda Lookvalue de resultados multiple

  Hola Comunidad, espero me puedan ayudar, tengo el siguiene problema en una tabla tengo los siguientes valores   Tabla de cabecera Historica     Tabla de datelle Historica     ...
General Comment
Need Help
  • v-alq-msft's avatar
    v-alq-msft
    5 years ago

    Hi, RicardoLeivaG 

     

    Based on your description, I created data to reproduce your scneario. The pbix file is attached in the end.

    Historic Header:

     

    Historic datelle:

     

    Here are the m codes for each Query.

    Historic Header:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hcsxCsAgDIXhu2QWYhIS9QKFLnVwKYj3v0aD0CIuzZT3w9c71Jb8IABF5IIcOfq4DxLTvOd2wgjTqKnvgpQWwjrJWj9hLObbkHUVonv9EcJW/MtI9tarwhgP", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Orden = _t, #"fecha Orden" = _t, #"Cod Invoice" = _t, #"Fcha Invoice" = _t, #"Cumple plazo entregra" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Orden", type text}, {"fecha Orden", type date}, {"Cod Invoice", type text}, {"Fcha Invoice", type date}, {"Cumple plazo entregra", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Orden"}, {{"Result", each Table.Max(_,"Fcha Invoice"), type table [Orden=nullable text, fecha Orden=nullable date, Cod Invoice=nullable text, Fcha Invoice=nullable date, Cumple plazo entregra=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each [Result][Fcha Invoice]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each [Result][Cumple plazo entregra]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Result", "Custom"})
in
    #"Removed Columns"

     

    Historic datelle:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8g82BwIlHSVDA30jS30jAyMDIKckMScxpSgfJAzFwZ5KsTpg5aZmpkC+pb6hOUx1bmJRSWZODkg5mG8IUuDnD9VgZmRsBuSb6RuZwo3PzwWbbQQj4KZDFVvoG5rBTU8tTlRISVVISsxLBukyhboIZEEsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Orden = _t, #"fcha Orden" = _t, Articulo = _t, #"cantidad ordenada" = _t, #"cantidad facturada" = _t, #"cumple despacho" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Orden", type text}, {"fcha Orden", type date}, {"Articulo", type text}, {"cantidad ordenada", Int64.Type}, {"cantidad facturada", Int64.Type}, {"cumple despacho", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Orden"}, {{"Result", each Table.Max(_,"fcha Orden"), type table [Orden=nullable text, fecha Orden=nullable date, Articulo=nullable text, cantidad ordenada=nullable number, cantidad facturada=nullable number, cumple despacho=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Fcha Orden", each [Result][fcha Orden]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Cumple despacho", each [Result][cumple despacho]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Result", "Fcha Orden"})
in
    #"Removed Columns"

     

    Query1:

    let
    Query1 = let
    Source1 = #"Historic Header",
    Source2 = #"Historic datelle",
    tab = Source1
in
    Source1,
    #"Added Custom" = Table.AddColumn(Query1, "Custom", each let orden = [Orden],
x = Table.First( Table.SelectRows(#"Historic datelle",each [Orden]=orden))[Cumple despacho]
in 
x),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.2", each if [Custom.1]="NO" or [Custom]="NO" then "NO" else "SI")
in
    #"Added Custom1"

     

    Result:

     

    Best Regards

    Allan

     

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

     

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi RicardoLeivaG 

 

Possible solution:

 

Tabla de cabecera Historica (please note the grouping added to the data to provide AND operation):

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQr2VIrViVYyQjCNUZl+/kqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Value1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Value1", type text}}),
    
    
//Add this line - this is to provide logical AND for all items under one order     
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"Value1", (x)=> not List.Contains(x[Value1], "no", (r, l)=> Text.Lower(r) = Text.Lower(l))}})

in
    #"Grouped Rows"

 

Tabla de datelle Historica (similar to above, note added grouping)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQrOVIrViVYyAjL98sFMY4SoMVQ0FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Value2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Value2", type text}}),

//Add this line - this is to provide logical AND for all items under one order     
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"Value2", (x)=> not List.Contains(x[Value2], "no", (r, l)=> Text.Lower(r) = Text.Lower(l))}})

in
    #"Grouped Rows"

 

Summary table:

let
    Source = Table.NestedJoin(cabecera, {"ID"}, datelle, {"ID"}, "datelle", JoinKind.LeftOuter),
    #"Expanded datelle" = Table.ExpandTableColumn(Source, "datelle", {"Value2"}, {"Value2"}),
    #"Added Custom" = Table.AddColumn(#"Expanded datelle", "Value3", each [Value1] and [Value2])
in
    #"Added Custom"

  

Kind regards,

JB