Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Split column into multiple rows by position

Dear experts, I'm looking for a DAX expression to split a column into multiple rows by number of characters (not delimiter). I can find many posts about splitting by delimiter but not splitting by p...
Split column
  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    4 years ago

    Hi Anonymous ,

     

    DAX can use something like the following:

    Table =
VAR _G =
    GENERATESERIES( 0, 5, 1 ) // 1 column to 6 rows
VAR _A =
    ADDCOLUMNS(
        CROSSJOIN( 'sample data', _G ),
        "PDF",
            MID( [Data], 6000 * [Value] + 1, 6000 )
    )
RETURN
    GROUPBY( _A, [DocID], [PDF] )

     

    Unfortunately, CROSSJOIN() is limited in Direct Query. If you want to know  detail about the DAX , i put the pbix file in the end you can refer.

    Maybe you can use M function to connect to file dynamically via parameters which is a path list of PDFs.

    https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-dynamic-m-query-parameters 

     

    Best Regards

    Community Support Team _ chenwu zhu

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Thank you all for your responses.

I'm trying to display a PDF file in the report using the visual "PDF Viewer", following the instruction in the site https://customertell.com/displaying-pdf-files-stored-in-sql-server-in-power-bi/

The challenge is I have about 5000 PDF files stored in binary format. I cannot use Direct Query mode with the steps mentioned in the instructions in the website. When I import all of them, my pbix grows beyond 3 GB and I find it difficult to work with the file. So, I was thinking to use Direct Query to bring one record at a time, and then perform the steps programatically. And for this, I should split the PDF content at 30000 characters into multiple lines. And I need the DAX for this step. I hope this time the requirement is clear 🙂

Here is a link to sample data

v-chenwuz-msft, I did that finally, but it would be great to understand how this can be done with DAX.

Regards, Vidyadutt

  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    Community Support
    4 years ago

    Hi Anonymous ,

     

    DAX can use something like the following:

    Table =
VAR _G =
    GENERATESERIES( 0, 5, 1 ) // 1 column to 6 rows
VAR _A =
    ADDCOLUMNS(
        CROSSJOIN( 'sample data', _G ),
        "PDF",
            MID( [Data], 6000 * [Value] + 1, 6000 )
    )
RETURN
    GROUPBY( _A, [DocID], [PDF] )

     

    Unfortunately, CROSSJOIN() is limited in Direct Query. If you want to know  detail about the DAX , i put the pbix file in the end you can refer.

    Maybe you can use M function to connect to file dynamically via parameters which is a path list of PDFs.

    https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-dynamic-m-query-parameters 

     

    Best Regards

    Community Support Team _ chenwu zhu

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.