none
How to join two table columns without matching any key column RRS feed

  • Question

  • I have two tables which are Country and Country_number both the tables contains only one column and they are in text datatype and data is not matching with each other, so data looks like below (Cartesian Join).

    I'm completely new to this Power BI reporting tool.

    Country table:

    US

    Canada

    India

    Russia 

    Country_number Table:

    3

    3

    3


    how can i achieving result like below in query editor table

    Country         Country_number

    US                       3

    US                       3

    US                       3

    Canada                3

    Canada                3

    Canada                3

    India                   3

    India                   3

    India                   3

    Russia                 3 

    Russia                 3 

    Russia                 3 

    Thanks in advance !!

    Tuesday, April 3, 2018 12:53 PM

Answers

  • Hey,

    You can create a new custom column and use the name of the table that you want in the formula section. Once you have that new column with table values, you can expand it and get the values that you need. 

    If you just want to repeat the values X amount of times then it would be easier to create something like a list inside that new custom column using a formula like {1..3} which would run way faster.

    • Marked as answer by SBS152 Wednesday, April 4, 2018 9:56 AM
    Tuesday, April 3, 2018 3:18 PM

All replies

  • do you want to get the Cartesian product, or is it about binding the two columns next to each other?

    do they have the same number of rows?

    Tuesday, April 3, 2018 1:31 PM
  • Hi Marcin,

    I need Cartesian product, both columns have different number of rows.

    In SQL the query will be like below

    SELECT t1.Name,           
           t2.Country_number      
    FROM Country t1, Country_number t2;

    With out using join condition i would like to join and result should look like as mentioned above.

    • Edited by SBS152 Tuesday, April 3, 2018 2:41 PM
    Tuesday, April 3, 2018 2:05 PM
  • Hey,

    You can create a new custom column and use the name of the table that you want in the formula section. Once you have that new column with table values, you can expand it and get the values that you need. 

    If you just want to repeat the values X amount of times then it would be easier to create something like a list inside that new custom column using a formula like {1..3} which would run way faster.

    • Marked as answer by SBS152 Wednesday, April 4, 2018 9:56 AM
    Tuesday, April 3, 2018 3:18 PM
  • in PowerQuery add new custom column in the Country table equal to Country_number table

    =Country_number

    then expand the new column

    Tuesday, April 3, 2018 3:18 PM
  • Thanks for your quick help Miguel :)
    Wednesday, April 4, 2018 11:02 AM