1

I have a large data set with a single date index and I need to append all the columns into a single one using the same date index.

i'm on a plain excel file with power query / dax (No Power Bi).

Example:

index Col 1 ... Col N
1 A ... X
2 B ... Y
3 C ... Z

Desired result:

index Col 1
1 A
2 B
3 C
... ...
1 X
2 Y
3 Z

Any help is appretiated. Thank you.

Atlahua
  • 23
  • 5

1 Answers1

0

For these kind of operations ideally use Power Query: Paste this code into Power Query's editor

let
    Source = Table.FromRows (
        Json.Document (
            Binary.Decompress (
                Binary.FromText (
                    "i45WMlTSUXIE4gilWJ1oJSMgywmII8E8YyDLGYijlGJjAQ==", 
                    BinaryEncoding.Base64
                ), 
                Compression.Deflate
            )
        ), 
        let
            _t = ( ( type nullable text ) meta [ Serialized.Text = true ] )
        in
            type table [ index = _t, #"Col 1" = _t, #"Col N" = _t ]
    ), 
    #"Changed Type" = Table.TransformColumnTypes (
        Source, 
        { { "index", Int64.Type }, { "Col 1", type text }, { "Col N", type text } }
    ), 
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns (
        #"Changed Type", 
        { "index" }, 
        "Attribute", 
        "Value"
    )
in
    #"Unpivoted Other Columns"

enter image description here

But to do the same in DAX use this:

Table = 
GENERATE ( 
    DISTINCT ( Atlahua[index] ),
    UNION ( 
        CALCULATETABLE ( DISTINCT ( Atlahua[Col 1] ) ),
        CALCULATETABLE ( DISTINCT ( Atlahua[Col N] ) )
    )
)

enter image description here

AntrikshSharma
  • 629
  • 1
  • 3
  • 12
  • Since I was in a hurry I picked the second solution and it worked just fine. However, it required Power Bi to generate the table since I understood that Excel doesn´t offer that feature so far. Nonetheless, I'll test the first solution which seems to work on Excel. Thank you so much! – Atlahua May 24 '21 at 13:58