Forum Discussion

siva_powerbi's avatar
siva_powerbi
Icon for Helper IV rankHelper IV
6 years ago

return the table of two selectcolumn commands using the table constructor

Hello experts

I have a requirement to combine every 2 columns of a table and then display the concatenated columns in a saperate table.

I am able to cross join and concatenate the result of the columns, but I cannot display the concatenated columns in a single table as the final result. At one point I can show only one column.

Code;

Table 2 =
Var col1 - SELECTCOLUMNS (Sheet2, "col1", Sheet2 [Test1])
Var col2 - SELECTCOLUMNS (Sheet2, "col2", Sheet2 [Test2])
was col3 - CROSSJOIN (col1, col2)
Var col4 - SELECTCOLUMNS (Sheet2, "col4", Sheet2 [Test2])
Var col5 - SELECTCOLUMNS (Sheet2, "col5", Sheet2 [Test3])
was col6 - CROSSJOIN (col4, col5)
Var col7 - SELECTCOLUMNS (Sheet2, "col7", Sheet2 [Test3])
Var col8 - SELECTCOLUMNS (Sheet2, "col8", Sheet2 [test4])
was col9 - CROSSJOIN (col7, col8)
var col10 - CONCATENATE (col4, col5)
return
SELECTCOLUMNS (ADDCOLUMNS (col6, "Concatcol", COMBINEVALUES (",", [col4], [col5])), "c1", [Concatcol])
Col3, Col6 and Col9 are the cross join columns now in the return code I have added for Col6, in the same way that I need to add col3 and Col9 and show all 3 columns in the calculated table.
Expected production
SELECTCOLUMNS (ADDCOLUMNS (col6, "Concatcol", COMBINEVALUES (",", [col4], [col5])), "c1", [Concatcol]),
SELECTCOLUMNS (ADDCOLUMNS (col3, "Concatcol1", COMBINEVALUES (",", [col1], [col2])), "c2", [Concatcol1])
I hope its a way, but I am a beginner in Power BI.
Sample data
Test1Test2Test3test4
oneoneJobsone
oneoneWorks1one
onetwoJobs2one
one3Jobs3two
one3Jobs4one
one4Jobs5one
Output I got

In the above image there is a column with the cross join and concatenation of Test2 and Test3, My requirement is to add 2 more columns with the concatenation of Test1 and Test2 and the concatenation of Test3 and Test4

Thank you
If you
Need Help
Show and Tell
Tips and Tricks

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    6 years ago

    siva_powerbi Sorry, having trouble following, can you post sample data as text and expected output?

     

    Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

    Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

    The most important parts are:
    1. Sample data as text, use the table tool in the editing bar
    2. Expected output from sample data
    3. Explanation in words of how to get from 1. to 2.

    • siva_powerbi's avatar
      siva_powerbi
      Icon for Helper IV rankHelper IV
      6 years ago

      Sir, Just updated the question, Please check and advice.

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    siva_powerbi , too many cross join. Please let us know what are you try to achieve.

    Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

    • siva_powerbi's avatar
      siva_powerbi
      Icon for Helper IV rankHelper IV
      6 years ago

      I have added the sample data in the question, it is the data I am working on.

      My requirement is to out of 4 columns.

      Make cross join of col1, col2 and col2,col3 and col3,col4

      Then concatenate cross joins with "," and show the resultant values in a table side by side.

      I tried to attach the file but it doesn't carry the data in datasource, May I know the exact process.

      Thanks