Is it possible to group existing columns into a table or perhaps a new calculated column?

[Danzlo]

Hi!

I have three different tables with a column named "Job Type". Two of the columns are already grouped with data from the table into a "grouped column".

I would like to either create a new table with the three columns combined or just a new calculated column with these values.

Is it possbile? Will illustrate my need below:

Kind Regards
Daniel

[TheoC · Accepted solution]

Hi @Danzlo 

Certainly is possible. Here are a few links:

https://stackoverflow.com/questions/66681505/dax-power-bi-summarize-table-based-on-two-columns

https://www.sqlbi.com/articles/all-the-secrets-of-summarize/

The SQLBI is pretty great in terms of it's level of detail.   Ultimately, many ways that it can be done. Using DAX, you can use SUMMARIZE, among other functions. Alternatively, you can use Power Query to append / merge (https://radacad.com/append-vs-merge-in-power-bi-and-power-query) queries / tables.

Hope this helps 🙂

[TheoC]

Hi @Danzlo 

Mate, just a quick one, I wanted to show you the output of Append as New in Power Query with direct reference to your scenario (apologies I didn't put this together sooner - I only realised you had provided a breakdown of your tables in your initial post after I looked at it on my computer rather than the phone).

I created two dummy tables in Table1 and Table 2 like below.  The below is in Power Query and I have combined two screenshots to show you that the two tables are unique. Note that there is no relationship between these and when you use Merge or Append, there is absolutely no need.  

In Power Query, under the Home tab in the main ribbon, go to the Combine section and click the down arrow on to the right of "Append Queries".  You will see "Append Queries as New" - click this.

A pop up window will appear.  If you have two tables or less that you want to append, obviously select "two tables" otherwise, for three or more, you can click on "Three or more tables". Add your two tables; in this scenario, it is Table1 and Table2:

The output is a new query / table called Append1.  It pretty much gives you the output you were after 🙂

Just as a side note, Append is brilliant for scenarios where the same columns / structure exists (i.e. column titles, data types, number of columns, etc).  If you have a situation where you are not so much concerned as to the rows, but one table might have more columns, etc., than the other, you can use Merge Queries for this.

Hope this helps and again, sincere apologies for the delay in providing this.

The same can be done using DAX, however, given the simplicity and cleanliness of using Power Query to achieve this, you are far less likely to run into errors / problems down the track with you Data Model when using Power Query to manage this process.

All the best mate! 🙂

Theo

[Danzlo · Accepted solution]

Hi @TheoC 
Great explanations and info. Much obliged! However, the append method combines all tables and data into one which was not what i was after. I wanted to withdraw a single column from each table and combine them either into a new table or a calculated column.
I fixed this by creating a new table harcoded the data and created a one to many relationship to the other tables.

[TheoC]

@Danzlo Nice work! Am glad you managed to resolve, and in a nice fashion!

[TheoC · Accepted solution]

Hi @Danzlo 

Certainly is possible. Here are a few links:

https://stackoverflow.com/questions/66681505/dax-power-bi-summarize-table-based-on-two-columns

https://www.sqlbi.com/articles/all-the-secrets-of-summarize/

The SQLBI is pretty great in terms of it's level of detail.   Ultimately, many ways that it can be done. Using DAX, you can use SUMMARIZE, among other functions. Alternatively, you can use Power Query to append / merge (https://radacad.com/append-vs-merge-in-power-bi-and-power-query) queries / tables.

Hope this helps 🙂

[Danzlo]

Hi @TheoC and thank you for your reply.

I dont want to merge them together into one row. I want to create a distinct list of each value within all three job type columns.

Dont think that summarize will work since the columns contain strings only.

Do you have another example. I can provide you with the columns so that you can see for yourself perhaps?

[TheoC]

Hi @Danzlo,

In Power Query, there is "Group By" and "Unpivot". If you send me an example of the data in current form and how you want it structured, I can prepare a PBIX for you and share it with you?

Thanks heaps,

Theo