Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
TheoC
Super User
Super User

Transpose Alternative?

Hi all,

 

I am using a Folder as source with approx 10 to 15 XLSX files.  Each file has approx 60,000 rows which isn't much, however, the columns range between 65 to 80. 

As can be seen in the below image, the objective is to use an equivalent of "FILL RIGHT".  To achieve this, I am transposing the table, then filling down on values... the only issue is it is taking a significantly long time to process (i.e. over two hours).  It does work but, as mentioned, takes a long time. 

 

TheoC_0-1619693665072.png

 

The above snapshot is an example of the current structure. By Transposing the table, I can then Fill Down, effectively allowing me to have Column 6 listed as "CAOT2".  Similarly, Column 8 would become "CAS_REG". The above goes on to column 80.  Does anyone know of work around other than Transpose or has anyone experienced similar timing issues?  

 

Thanks heaps in advance!

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

1 ACCEPTED SOLUTION
CNENFRNL
Community Champion
Community Champion

You may try this way, which doesn't get involved in any transposing.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUUoEYggyBFGxOtFKThB+EhCbQmVBws5wlUBkBhN1gQulQCjXvBSwhCuycrDqWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", Int64.Type}, {"Column6", type text}}),

    #"Fill Right" = Table.FromRows(
        let cols=Table.ColumnCount(#"Changed Type")
        in List.Accumulate(
            Table.ToRows(#"Changed Type"),
            {},
            (seed,row) => seed & {List.Accumulate({1..cols-1}, {row{0}}, (s,c) => if Text.Length(Text.From(row{c}??""))=0 then s&{s{c-1}} else s&{row{c}})}
        ),
        Table.ColumnNames(Source)
    )
in
    #"Fill Right"

Untitled.png


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

View solution in original post

5 REPLIES 5
CNENFRNL
Community Champion
Community Champion

You may try this way, which doesn't get involved in any transposing.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUUoEYggyBFGxOtFKThB+EhCbQmVBws5wlUBkBhN1gQulQCjXvBSwhCuycrDqWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", Int64.Type}, {"Column6", type text}}),

    #"Fill Right" = Table.FromRows(
        let cols=Table.ColumnCount(#"Changed Type")
        in List.Accumulate(
            Table.ToRows(#"Changed Type"),
            {},
            (seed,row) => seed & {List.Accumulate({1..cols-1}, {row{0}}, (s,c) => if Text.Length(Text.From(row{c}??""))=0 then s&{s{c-1}} else s&{row{c}})}
        ),
        Table.ColumnNames(Source)
    )
in
    #"Fill Right"

Untitled.png


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

Out of curiousity, is there a way to apply the above code to a Folder as source that contains XLSX files that have a varied number of columns?  None of the XLSX files have more than 100 fields (if there is a requirement of a fixed number).

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

@CNENFRNL Thanks heaps for this. Will give it a go. It looks like it is exactly what I am looking for!

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

amitchandak
Super User
Super User

@TheoC , Transpose or unpivot are the best suited; looking what I got as of now.

 

 

@amitchandak Thanks for the response!  I did apply the transpose, however, given its resource-intensive approach to process large data sets and tables, it was not the ideal approach.  As mentioned in my post, it did work effectively, but it was not efficient nor timely.  Thanks again for taking the time to respond and for providing a solution that works well on smaller data sets and smaller tables! 🙂

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.