Combine tables with SQL in power bi

  • Hello everyone, i hope everyone is fantastic!!

    I've done some SQL queries in power bi to get my data and do some ETL. I have 6 companies, therefore, 6 queries.

    Now i would like to append them, combine(?) them in order to have just one sales table.

    What would you think it would be my best way to achieve that?

    Thanks in advance all of you

    Best regards

    Pedro

  • Does the data come from a single database? Performance-wise, I'd suggest doing as much of the 'heavy-lifting', in terms of ETL and combining result sets, using the SQL Server database engine.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Hello Phil and thank you for your answer first of all

    The data come from each database, I mean, each company has its own database. 6 or 7 lines for each SQL queries.

    If there's a way of doing the combine all 6 queries, I really don't know how, unless maybe you repeat all those queries in a single one and at the end, just combine them. That way, i believe, it will take some time to do it all....

    Maybe i should leave it as it is....?I mean, let power query's M do it?

  • M can do it, but if you have hundreds of thousands of rows of data, it will take a while.

    Are you just looking for help with the necessary M syntax?

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Phil, the M query is as simple as this one :

    let

    Source = Table.Combine({#"Sales Comp1", #"Sales Comp2", #"Sales Comp3", #"Sales Comp4", #"Sales Comp5", #"Sales Comp6"})

    in

    Source

    and this dataset compared with the original is like 10 times faster (at least) because the previous queries are all done in SQL. Maybe if i had write permissions in this database, i could build visuals and do something like this:

    SELECT Sales Comp1 UNION Sales Comp2 UNION Comp3....and so on

    I think i will leave it as it is because M is also faster, not as much as SQL of course, but still. Do you agree with me?

  • Your suggested query should use UNION ALL rather than UNION (which applies an unwanted (in this case) 'DISTINCT' to the results returned). Only 'read' access is required for this query.

    However, if doing it in M is working for you at a reasonable speed, sounds like life is good 🙂

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Phil, I love the simple solutions and I am tempted to leave things as they are now.

    Thank you so much for your clarifications, always enlightening.

    Best regards

    Pedro

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply