Summarize columns and repeat information

[Anonymous]

Hi there,

Is it there any way so that I can append a column under another, however keeping information of further columns? What I aim to do is something akin to the following example (here is a .pbix file I've created in order to illustrate my point).

So I was able to create it in Power Query, however my original file has more than three hundred thousand rows and a lot of columns, so if it is possible to create in DAX it would definitely help me a lot because in Power Query it takes a way too longer than it certainly takes in DAX.

Thanks in advance.

[tamerj1]

@Anonymous 
Even more simple and dynamic solution

Appended2 = 
UNION (
    ALLEXCEPT ( Main_tbl, Main_tbl[Salesperson2] ),
    ALLEXCEPT ( Main_tbl, Main_tbl[Salesperson] )
)

Go for DAX. It is much more faster and much easier.

[CNENFRNL]

Interesting idea; but the only thing worth mentioning is that ALLEXCEPT(), when used as table function, returns a summarized table, like what SUMMARIZE() does.

[CNENFRNL]

Easy task in PQ and flexible regardless of column names.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsxJK0pNUdJR8kjNK6oE0qYGQMLAVN/ISN/IwMgIyHHLzEvMS04tVorViVZySi3KSyxC1mBhiq4huKQosSQ1PTMZrMM7sSg1D5sFBjD1volF2aklmXnpYPUBqSWpRUjqLQ0h6g0tsZsPUxeSkZ+bWKzgqwdykzlUjwV2OxC+QNZlZoauC8XrMI+gaMGwCEULzC8objPBE16xAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Salesperson = _t, Salesperson2 = _t, Target = _t, Date = _t, Area = _t]),

    Reshaped = let cols = Table.ToColumns(Source), names = List.RemoveRange(Table.ColumnNames(Source),1) in Table.FromColumns(List.Skip(cols),names) & Table.FromColumns(List.RemoveRange(cols,1),names)
in
    Reshaped

[Anonymous]

Thanks, man. I couldn't find a way to make it work using DAX (now @tamerj1  showed a way), and if I did it using PQ it would properly work.

[tamerj1 · Accepted solution]

Hi @Anonymous 
You can use

Appended = 
UNION (
    SELECTCOLUMNS ( Main_tbl, "Salesperson", Main_tbl[Salesperson], "Area", Main_tbl[Area], "Date", Main_tbl[Date], "Target", Main_tbl[Target] ),
    SELECTCOLUMNS ( Main_tbl, "Salesperson2", Main_tbl[Salesperson2], "Area", Main_tbl[Area], "Date", Main_tbl[Date], "Target", Main_tbl[Target] )
)

[Anonymous]

Thank you very much, @tamerj1 . It worked just fine.