Forum Discussion
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 position.
Can you please help me?
Regards, Vidyadutt
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.
6 Replies
Anonymous posts with sample data move very fast in this forum.
It helps to be clear about what the ultimate result should be too.
Anonymous Are you trying to define a calculated column/table or a measure to use in a visual or something else?
Hi Anonymous ,
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.
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 🙂
v-chenwuz-msft, I did that finally, but it would be great to understand how this can be done with DAX.
Regards, Vidyadutt
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.
