Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Summarize columns and repeat information

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.

Need Help
Tutorial Requests
  • tamerj1's avatar
    tamerj1
    4 years ago

    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] )
)

6 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

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

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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's avatar
      CNENFRNL
      Icon for Community Champion rankCommunity Champion
      4 years ago

      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's avatar
    CNENFRNL
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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.