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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.