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.

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

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.