Splitting Columns

[mussaenda]

Anyone to help me with this?

I need to turn the column like this:

5000 10000 15000 20000 25000 30000 35000 40000 45000 50000 55000 60000 65000 70000 75000 80000 85000 90000 95000 100000 105000 110000 115000 120000 125000 130000 135000 140000 145000 150000 155000 160000 165000 170000 175000 180000 185000 190000 195000 200000 205000 210000 215000 220000 225000 230000 235000 240000 245000 250000 255000 4100 9100 14100 19100 24100 29100 34100 39100 44100 49100 54100 59100 64100 69100 74100 79100 84100 89100 94100 99100 104100 109100 114100 119100 124100 129100 134100 139100 144100 149100 154100 159100 164100 169100 174100 179100 184100 189100 194100 199100 204100 209100 214100 219100 224100 229100 234100 239100 244100 249100 254100 3200 8200 13200 18200 23200 28200 33200 38200 43200 48200 53200 58200 63200 68200 73200 78200 83200 88200 93200 98200 103200 108200 113200 118200 123200 128200 133200 138200 143200 148200 153200 158200 163200 168200 173200 178200 183200 188200 193200 198200 203200 208200 213200 218200  223200 228200 233200 238200 243200 248200 253200 2300 7300 12300 17300 22300 27300 32300 37300 42300 47300 52300 57300 62300 67300 72300 77300 82300 87300 92300 97300 102300 107300 112300 117300 122300 127300 132300 137300 142300 147300 152300

Current column is this:

5000100001500020000250003000035000400004500050000550006000065000700007500080000850009000095000100000105000110000115000120000125000130000135000140000145000150000155000160000165000170000175000180000185000190000195000200000205000210000215000220000225000230000235000240000245000250000255000410091001410019100241002910034100391004410049100541005910064100691007410079100841008910094100991001041001091001141001191001241001291001341001391001441001491001541001591001641001691001741001791001841001891001941001991002041002091002141002191002241002291002341002391002441002491002541003200820013200182002320028200332003820043200482005320058200632006820073200782008320088200932009820010320010820011320011820012320012820013320013820014320014820015320015820016320016820017320017820018320018820019320019820020320020820021320021820022320022820023320023820024320024820025320023007300123001730022300273003230037300423004730052300573006230067300723007730082300873009230097300102300107300112300117300122300127300132300137300142300147300152300157...

[JarroVGIT]

Is this a column name? Or is this a value in a column?

If it is the latter, in DAX I would substitute every combination of 0 and [1-9] with 0 <space> [1-9], that seems to work for your value. 

Measure = 
VAR a01 = SUBSTITUTE(FORMAT(Table[Column1], "#"), "01", "0 1")
VAR a02 = SUBSTITUTE(a01, "02", "0 2")
VAR a03 = SUBSTITUTE(a02, "03", "0 3")
VAR a04 = SUBSTITUTE(a03, "04", "0 4")
VAR a05 = SUBSTITUTE(a04, "05", "0 5")
VAR a06 = SUBSTITUTE(a05, "06", "0 6")
VAR a07 = SUBSTITUTE(a06, "07", "0 7")
VAR a08 = SUBSTITUTE(a07, "08", "0 8")
RETURN
SUBSTITUTE(a08, "09", "0 9")

This works because your sequence is a set of numbers ending in 0's.

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Kudo's are welcome 🙂

[mussaenda]

Wow, thanks @JarroVGIT ! Nice.

I will definitely ty this. But do you have any idea how to do this in Power Query?

Thank you

[JarroVGIT · Accepted solution]

That would require zero code (just GUI stuff). I have a table with 1 column and 1 row (the value you gave):

Note that I changed the type to TEXT

Right click on column name, choose 'Replace Values' and enter "01" to be replaced with "0 1" (no quotes). Repeat for 02, 03 etc. 

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Kudo's are welcome 🙂

[mussaenda]

Works like a charm! Thanks @JarroVGIT