Split column into multiple rows by position

[vidyadutt]

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 position.

Can you please help me?

Regards, Vidyadutt

[vidyadutt]

Thank you very much for the tip. I'll check and explore the possibilities with M.

Regards, Vidyadutt

[vidyadutt]

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 · Accepted solution]

Hi @vidyadutt ,

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.

[v-chenwuz-msft]

Hi @vidyadutt ,

Maybe you can you FIND() to get the position and use LEFT/RIGHT/MID to get the result.

But split column in power query editor is better then DAX. DAX expressions are good at analysing data, Power query editor is good at shaping data.

You can share some example data and expect result, i will help you.

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.

[smpa01]

@vidyadutt  posts with sample data move very fast in this forum.

[AlexisOlson]

It helps to be clear about what the ultimate result should be too.

@vidyadutt Are you trying to define a calculated column/table or a measure to use in a visual or something else?