DAX UNION Fn or Advance Editor to Append two column values

[Benjosh]

My Input is from API & added to a single table.

The values of SubGroup_Name & USG_Name should be added to a new column output

Output

Residential

Residential

Others

Others

Residential

Other

Waiting for the solution, if needed more clarity will be provided on reply

[parry2k]

@Benjosh In PQ, start a new blank query, click advanced editor, remove everything and copy the following code. it contains sample data and the new output column, you can replicate the same in your actual table.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXJSitWJBpI6Ss5gljOQ5agUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"New Table" = Table.RenameColumns(Table.FromList(Table.Column(Source,"Column1")),{{"Column1", "Output"}}),
    #"Added Custom" = Table.AddColumn(#"Source", "Output", each [Column2]),
    #"Appended Query" = Table.Combine({#"Added Custom", #"New Table"})
in
    #"Appended Query"

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[parry2k · Accepted solution]

@Benjosh what is the logic to get the output column that doesn't make any sense in what order these values are appended? Man, you need to be very clear, I cannot think what you are looking for. There is no logical explanation of how this output column is created. You just cannot randomly give the output and not explain it.

[Benjosh]

@parry2k I understood now, My question is wrong. I accept the given solution.

[Benjosh]

@parry2k I don't mind the order, If there is a logic giving me in order for my requirement, then I'm happy to take it.

I just need these 2 column data appended in the same table in a new column with any order or logical possible order.

[parry2k]

@Benjosh your question is a bit confusing, what happens to other columns, that get appended too. You need to clearly define the requirement, having these two-column and appending doesn't make sense if you want it as a new column in the same table. Create a dummy table of your raw data and the final output and explain to provide the solution.

Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Benjosh]

@parry2k Below attached the dummy data with the required output from the input.

Single table with the input & the appended output in the new column.

[parry2k]

@Benjosh ok, here is what you can do to create a table appended from both the columns:

New Table = 
UNION (      
    SELECTCOLUMNS ( YourTable, "New Column", YourTable[USG_Name ), 
    SELECTCOLUMNS ( YourTable, "New Column", YourTable[SUBGROUP_Name] )
) 

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Benjosh]

@parry2k But I want a New Column, not New Table.

If separate table I need to create a relationship between these two (existing & new) for my functions...

[parry2k]

@Benjosh not clearly how your outcome has only 6 rows whereas your sample data has 6 rows and if two columns are appended it will be 12 rows, 6 with residential value and 6 with others? Am I missing something?

 Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Benjosh]

@parry2k Sorry I was lazy to type the complete output. 

Yes ur correct the ouput is the appended values of both the columns with 12 rows.