Union tables in one query

[martinkitty]

Hello,
I am a quite new user of PowerBI and I wanted to take advantage of PowerQuery to prepare a dataset. I want to append two tables - one is a CSV file that I am importing and second comes from python script that I am executing. Python Script reads data from CSV file and uses it as a parameter to call API and then returns a new table. Finally, I want to append second table to my first table (like UNION operation in SQL) as the tables have the same columns. Unfortunately, PowerBI is performing FULL JOIN instead of UNION in my case. I have to do it in a single query as from what I know it's the only option to read data from CSV and use it in the python script. However, when I hardcoded parameters in Python script and run everything in seperate queries PowerBI appended those tables as I expected.
Here is my code(I deleted path to CSV and python file on purpose):

let
    Source = Csv.Document(File.Contents(""),[Delimiter=",", Columns=9, Encoding=1250, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"idVisit", Int64.Type}, {"visitorId", type text}, {"serverTimePretty", type datetime}, {"url", type text}, {"type", type text}, {"Mail", type text}, {"Name", type text}, {"Surname", type text}, {"Country", type text}}),
    #"Run Python script" = Python.Execute("")
    refreshed = #"Run Python script"{[Name="refreshed"]}[Value],
    #"Changed Type1" = Table.TransformColumnTypes(refreshed,{{"idVisit", Int64.Type}, {"visitorId", type text}, {"serverTimePretty", type datetime}, {"url", type text}, {"type", type text}, {"Mail", type text}, {"Name", type text}, {"Surname", type text}, {"Country", type text}}),
    Result = Table.Combine({Source, refreshed})
in
    Result

I would appreciate any suggestion how to append those tables.

[Jimmy801]

Hello @martinkitty 

Table.Combine is for sure the right function to use, and I don't know why it should not append tables within your query.

What I can see in your code however, is that you may reference the wrong step. Try to change your last step as follows

Result = Table.Combine({#"Changed Type", refreshed})

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Anonymous]

hi @martinkitty 

if your problem is that indicated from @Jimmy801 , then should try, to complete its advice, changing the expression

in this way:

 Result = Table.Combine({#"Changed Type", #"Changed Type1"})

But if you want to exactly simulate UNION SQL (I just see this sintax for first time)

as explainde here

"The UNION operator selects only distinct values by default."

you should use table.distinc (before and/or after the table.combine)

[Greg_Deckler]

@martinkitty - Table.Combine is the equivalent of a UNION if you have the same column names in both tables. Should be simple enough for you to get both tables to have the same column names, Table.RenameColumns.

It is not really doing a full join, to do that you use Table.Join. But, if you have different column names in your tables, you will get sort of an amalgamation of the two structures.

https://docs.microsoft.com/en-us/powerquery-m/table-combine

[Anonymous]

if i am not missing something, is it not enough to simply add the use of table.distinct?