Combining Multiple Columns into One

Hi All,

Curious to see how others approach this and for something so simple i had to resort to SQL instead of using the query editor (i have a strong feeling this is going to be user error). Even a quick google did not produce any straight froward answers which again for something so simple seems strange.

Table A, B and C all have multiple different columns in a different format.

I would like to take:

• Column 1 from table A
• Column 5 from table B
• Column 7 from table C

Append does not appear to work as all are a different format and does not combine into one column.

How would others approach this scenario?

Thanks

Hi @Hitman,

Thanks for the post.

Do you want to combine multiple data types into 1 column. In this case it may not be possible as a column of a table in Power BI cannot have multiple data types.

In some cases if we have numbers and text we always set these as Text.

Regards,
Harsh Nathani

Hi Harsh,

Data Type all the same and are text. Lets say data is a customer ref.

In table 1 it is call Cust ID
table 2 Reference
Table 3 Customer Name

@Hitman,

Here’s one way it could be done via DAX:

Columns to Text Table = 

VAR vTable1 =
CALCULATETABLE(
     VALUES(Sales[Quantity] ),
     FILTER(
        Sales,
        Sales[Quantity] <> BLANK()
    )
)

VAR vTable2 =
VALUES(Products[Product Name] )

VAR vTable3 =
VALUES( Customers[Customer ID] )

RETURN
UNION(
    vTable1,
    vTable2,
    vTable3
)

image

So here’s one way of doing that in Power Query

Use the List.Combine function and reference each Table[Column] between the list initializer signs { }
You could also wrap List.Distinct around it if you only want unique values and finally you can turn the result into a Table.

I hope this is helpful.

2 Likes

Hi @Hitman,

You can try

Create a new Table

Join Multiple Tables to one Column = 
VAR Vtab1 =
        VALUES ( Table1[Cust ID] )
    VAR vtab2 =
        VALUES ( Table2[Reference] )
    VAR vtab3 =
        VALUES ( Table3[Customer Name] )
    RETURN
        UNION (
            Vtab1,
            vtab2,
            vtab3
        )

Image shows different tables data getting merged into a single column in a new table

Regards,
Harsh Nathani

1 Like

Great to see how others do it - Thanks guys. I still think that for such a simple task it is not intuitive.

From an efficiency perspective the choice of Dax or Q.E will depend upon data source and query folding - Agreed?

Hi @Hitman,

You can check if Query Folding is happening by checking for the existence of Native Query code inside that step. To do this, right-click on one of the query steps in the Applies Steps Pane and then select “view Native Query”. If you are able to do this, ie if view Native Query is enabled, it means Power Query has successfully performed query folding.

Don’t expect the manually Query will fold but all others should, you are the only one who can judge if the performance has noticeably been affected. Generally I would do a task like this in Power Query but as shown you can do this with DAX as well…

Hi Melissa,
I tried using your way for another task and had a few issues.

Process followed was:

  1. Home / Transform Data
  2. Home / blank Query
  3. In formula bar i typed “=List.Combine({List.Distinct(Table A[columnA]),List.Distinct(Table B[columnB])})”

However i expected it to have predictive text for tables that exist. It didnt and i type manually but it did not like it. What am i doing wrong please?

Also in your above screen shot i do not have the “Transform tab” on the far right. Any ideas why please?

Thanks

The syntax looks okay but that Transform tab should automatically appear when your Query returns a type List. So to troubleshoot check the results from each individual step before combining them:

  1. = Table A[columnA] (does this return a list?)
  2. wrap List.Distinct around it (do the number of rows decrease?)
  3. in a separate query do the same for Table B[columnB]
  4. if both return List objects then combine them in a List.Combine and don’t forget the { }

If building it from the ground up doesn’t solve it for you, please share a sample so members can assist
I hope this is helpful.

Think im being a muppet but…

Step 1 is not picking up a list. I have attached a screen shot (blurred out everything other than the query 1 with where i am attempting to create a list from Sales ledger table.

As you can see in the drop down the only sales ledger is # “sales ledger” and if i select this there is no option to select a column. Does that make sense?

Capture

Yes that makes sense.

PQ is able to pick up the Query names but not the Column names, you have to add that manually between a single pair of and depending on whether it includes special characters or whatever you might also have to include the hash in front and add double quote signs…

To avoid errors (because PQ is also case sensitive) I usually start by copying the column name from the original query and past that in between . When that doesn’t return a result and you see spaces or special characters add the hash and double quote signs like you see in the table reference: # “sales ledger”

I hope this is helpful.

Hi Melissa,

Thank you for being patient with me and the step by step approach did work. Love seeing different approaches and constantly learning new ways. I thought my Power Query was OK but realise now i need to invest some time. I have the standard books:

  • M is for Monkey
  • Collect, Combine, and Transform Data Using Power Query in Excel and Power BI (Business Skills).

Any resources you can recommend to stop me keep on asking you questions :wink:

Andrew

@Hitman,

We’ve been collectively gathering DAX, PQ/M and Visualization book recommendations. Here’s the thread below. The choices for PQ/M are very limited and you have the 2 most important ones:

In addition, lot of folks (myself definitely included) have asked Melissa how she got so proficient in PQ/M and how we can develop our own expertise in the same way. She has put together an excellent resource list here:

  • Brian

Thanks Brian & Melissa I think you should write a book and add your name to the list. I would buy it :slight_smile: