Combine tables with SQL in power bi

Question

Combine tables with SQL in power bi

pedroccamara

SSC-Addicted

Points: 431
More actions
July 5, 2022 at 7:02 am

#4058943

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

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

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

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

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.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

pedroccamara SSC-Addicted Points: 431 More actions · Answer 2

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?

Phil Parkin SSC Guru Points: 246960 More actions · Answer 3

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?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

pedroccamara SSC-Addicted Points: 431 More actions · Answer 4

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?

Phil Parkin SSC Guru Points: 246960 More actions · Answer 5

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 🙂

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

pedroccamara SSC-Addicted Points: 431 More actions · Answer 6

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