combinar consultas

[rdiazd73]

hola power bi-team

agradecere si me pueden ayudar, con un problema para obtener datos de una tabla a otra y que metodo utilizar

table1                                       table2
codigo precio marca          codigo precio marca
1              1           x                 1         ?            ?
1              2           x                  2        ?            ?
2              5           c                  3        ?            ?
3              7           b
3             7,5         b

teniendo dos tablas ,

con el mismo dato las dos que seria el codigo

necesito contruir en table 2  el precio y la marca que contiene la table 1

problema en la tabla 1 hay diferentes precios, por lo que podriamos coger el precio mas alto,

como segundo problema es que la tabla 1 contiene mas filas con la marca con lo que al combinar 

me da multiples resultados .

gracias x la ayuda

[V-pazhen-msft · Accepted solution]

@rdiazd73 
If you want to create report to include both tables values, you can create a many to one relationship for table 1=>table 2 using [codigo[ column,  then you can put the column in one table visual and do calculation on table 2 using related(table1[column]). 

If you want to put 2 tables in 1 table, you can just use merge or append feature in query editor. 

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

[AlB]

@rdiazd73 

And in PQ, if you have Table1, these would be the steps from table2:

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

    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Codigo"}, Table1, {"codigo"}, "Table1", JoinKind.LeftOuter),
    res = Table.AggregateTableColumn(#"Merged Queries", "Table1", {{"precio", List.Max, "Precio"}, {"marca", List.Max, "marca"}})
in
    res

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[AlB]

@rdiazd73 

Create these two calculated columns in Table2

Precio =
CALCULATE (
    MAX ( Table1[Precio] ),
    FILTER ( ALL ( Table1[codigo] ), Table1[codigo] = Table2[codigo] )
)

Marca =
CALCULATE (
    MAX ( Table1[Marca] ),
    FILTER ( ALL ( Table1[codigo] ), Table1[codigo] = Table2[codigo] )
)

All this can also be done in PQ

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[rdiazd73]

Hello Thank you x look at it

the code always corresponds to the same brand

the problem that has arisen me to merge tables is that Table 1 has hundreds of records

and table 2 a few, and it transforms table 2 into thousands of rows

Best regards

Ricard

[AlB]

Hi @rdiazd73 

So you want the highest pricein table2. That seems clear. What do you want for the brand? The brand that has the highest price? Another one? Please show the expected result

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers