Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
rdiazd73
Helper I
Helper I

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

 

1 ACCEPTED SOLUTION
V-pazhen-msft
Community Support
Community Support

@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.

View solution in original post

5 REPLIES 5
V-pazhen-msft
Community Support
Community Support

@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
Super User
Super User

@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 

SU18_powerbi_badge

 

AlB
Super User
Super User

@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 

SU18_powerbi_badge

rdiazd73
Helper I
Helper I

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
Super User
Super User

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 

SU18_powerbi_badge

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.