Forum Discussion
combinar consultas
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
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.
5 Replies
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
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
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
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 resPlease 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
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.
