Stacking multiple columns into single column in a table

[MulberyPie]

I would like to combine multiple columns from a seperate table into a single column in a new table, but I haven't found a way to do that in the table editor with dax. If I had the table in figure 1 how could I make it look like figure 2 but on a larger scale with 5-6 columns being put into a single column?

Figure 1

 

  

Figure 2

Thank You

[dhruvinushah]

Hi @MulberyPie  
I was able to get the result you wanted in Figure 2 shared in your question without creating a second or third table. Refer screenshot and the Power Query M code for more details. 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKshMV9JRSkosygNS6fn5xalAOicxO1UpVidaKTm/HCGbllmcAZeMBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Column2]), "Column2", "Column1"),
    #"Pivoted Column1" = Table.Pivot(#"Pivoted Column", List.Distinct(#"Pivoted Column"[Column4]), "Column4", "Column3"),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Pivoted Column1", {}, "Attribute", "Value")
in
    #"Unpivoted Columns"

Ultimately you should decide which solution is more optimum and easier to deploy for your use case.

I ended up using Pivoting the data two times to create this table:

After which you can simply Unpivot Column to get your ideal result. 

Hope this helps. 

Please give thumbs up, Thanks!

[MulberyPie]

This looks like a great option. Thank you very much!

[mahenkj2]

You may check with your actual scenario, theoretically it should work fine. Additionally, you should disable load of all such copies, so final data model shall only contain your desired table. This way your data size is optimised and user don't see spare tables.

[mahenkj2 · Accepted solution]

Hello @MulberyPie ,

Assuming you have no problem using power query, 

Make duplicate of say original table 1 as table 2.

Remove column value 3 and value4 from table1. Rename value 3 and 4 in table 2 as value 1 and value 2.

Then append table 2 in table 1 and you are done.

I can post an example file if it is acceptable but you would like to see an example.

[mahenkj2]

Before rename value 3 and value 4 in table 2, remove value 1 and value 2 column.

[MulberyPie]

So if I had values 5-8 that I wanted to append to 1&2 would I just have to create a lot of copies where everything is named value 1 & 2 and append them all?