Ayuda Lookvalue de resultados multiple

[RicardoLeivaG]

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

tabla resumen quedo contruir

Orden	Cumpel despacho	Cumple plazo de entrega	Cumple acuerdos
OS6236	NO	NO	NO
OS7777	SI	SI	SI
OS7565	NO	SI	NO

Necesito rescartar los datos que marcan el cumple, intente ocupar el LookUPvalue, pero me da un error porque dice que hay muchos campos de igual valor

[v-alq-msft · Accepted solution]

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]

Hi @RicardoLeivaG ,

Slightly modified to meet your requirements:

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)=> x[Value1]{List.PositionOf(x[ID], List.Max(x[ID]))}}})

in
    #"Grouped Rows"

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)=> x[Value2]{List.PositionOf(x[ID], List.Max(x[ID]))}}})

in
    #"Grouped Rows"

Just change Value1 & Value2 to your values and ID (which we do LIst.Max for to your date field.

Kind regards,

JB

[Anonymous]

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

[Anonymous]

Hi @RicardoLeivaG 

¿Cómo decide qué valor tiene OS6236? Hay dos líneas para este artículo.

Gracias,

JB

[RicardoLeivaG]

Perdon pense que lo habia colocado. pero debo traer el valor mas alto, en este caso la fecha de la Invoice, y almacenar el NO en la tabla que debo construir