crear una tabla de una relacion de otras dos

[Tofe]

Hola a todos, quiero crear una relacion entre dos tablas de la siguiente manera, mi primer tabla tiene los siguientes datos,

 y la segunda tiene,

 entonces quiero tomar el articulo, que lo busque que la tabla 2 y luego regrese a la tabla uno tome fecha de facturacion y vea en que rango de fechas entra, en este caso para el articulo 1111396 encontro dos coincidencias pero la fecha facturacion solo esta en el segundo intervalo de fechas, 

por lo tanto quiero que mi tabla resulte sea mi tabla1, mas la coincidencia de la tabla2 con respecto al material

, alguna idea de como podria hacerlo en power BI? les agradezco mucho a todos.

[Anonymous · Accepted solution]

Hi @Tofe ,

I suggest you to use Merge function in Power Query Editor and then add code to filter your merged table.

Click Merge Queries > Merge Queries as New > select key columns [Material] and [Client] by Ctrl + Left click . 

Then Expand [vigenica de], [vigenica a] and [costo unitario] from second table.

Finally, add below filter code in Advanced Editor to filter table dynamiclly.

...
    #"Filtered Rows" = Table.SelectRows(#"Expanded Second", each ([Invoice Date] >= [Second.vigencia de] and [Invoice Date] <= [Second.vigencia a]))
...

The Whole M code:

let
    Source = Table.NestedJoin(First, {"Material", "Client"}, Second, {"Material", "Cliente"}, "Second", JoinKind.LeftOuter),
    #"Expanded Second" = Table.ExpandTableColumn(Source, "Second", {"vigencia de", "vigencia a", "costo unitario"}, {"Second.vigencia de", "Second.vigencia a", "Second.costo unitario"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Second", each ([Invoice Date] >= [Second.vigencia de] and [Invoice Date] <= [Second.vigencia a])),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Second.vigencia de", "Second.vigencia a"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Second.costo unitario", "costo unitario"}})
in
    #"Renamed Columns"

Result:

 

Best Regards,
Rico Zhou

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

[Tofe]

Hola, Muchas gracias, se parece mucho a lo que estaba haciendo, solo que en ambos intentos cuando continuo con lo que necesito, me deja de reconocer la fecha, sabria usted por que y como podria solucionar?

[Anonymous]

Hi @Tofe ,

You seem to use Group by Funtion in next step. However your reply and screenshot are in Spanish in my side. So it is hard for me to understand your next step clearly. Could you share a sample file with me and show me a screenshot with the result you want? It will make me easier to find the solution.

Best Regards,
Rico Zhou

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

[lbendlin]

Depending on the size of your dataset you may get away with using LOOKUPVALUE() for this.