Forum Discussion

martinkitty's avatar
martinkitty
New Member
6 years ago

Union tables in one query

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.

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

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

     

     

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    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

  • Jimmy801's avatar
    Jimmy801
    Community Champion
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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)