Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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.
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
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
¿Cómo decide qué valor tiene OS6236? Hay dos líneas para este artículo.
Gracias,
JB
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.