Power Query Split by Column Each Occurrence giving error

[Mubar22]

Hi Devs

I want to split this column by comma, into each occurence to have it's respective column, but when i used the each occurence using comma as my delimiter, I only have two columns splitted when I was expecting like 50 columns.

Also, it shows this preview warning before returning splitted column. is there a way I can work around this.

Thanks,

Mubar

[serpiva64]

Hi,

as you see i can't get your error and your data are splitted in 323 columns

Maybe, at a certain point of your data you get some problem.

Pay attention to the fact that the error isn't on split by column but in a further step you have created

[Mubar]

This is what I'm having when I do it at my end

I only have 2 columns splitted

[ronrsnfld]

I, also, get 323 columns splitting your data on the comma. Please go into the Advanced Editor and post your M-Code as text so we can examine it.

[Mubar · Accepted solution]

It's working fine now, I used the advanced features to split it to 200 columns (it was 2 on default)

Thanks

[serpiva64]

Hi,

please post some rows of your table in order to understand the problem

[Mubar]

Here is content in the rows

[serpiva64]

sorry, but if you paste using copy and paste the content of 3 or more cells instead of the image it is easier to try to solve (on the contrary i need to copy the content and it will me take a long time)  

[Mubar]

Okay, 

{"page":1,"total":30,"limit":50,"data":[{"id":33,"name":"TRUCK","code":"TRK","description":"","is_active":true,"is_internal":false,"created_at":"2023-08-08T13:44:06.589Z","updated_at":"2023-08-08T13:44:22.630Z","created_by_user":{"id":"72f64fa0-d865-42b4-b4b8-f0395f2cb2a7","first_name":"Daniel","last_name":"Emechete"},"updated_by_user":{"id":"72f64fa0-d865-42b4-b4b8-f0395f2cb2a7","first_name":"Daniel","last_name":"Emechete"}},{"id":32,"name":"BULK","code":"BLK","description":"Bulk orders from 300 bags and above","is_active":true,"is_internal":true,"created_at":"2023-03-23T07:36:34.078Z","updated_at":"2023-03-23T07:37:00.709Z","created_by_user":{"id":"72f64fa0-d865-42b4-b4b8-f0395f2cb2a7","first_name":"Daniel","last_name":"Emechete"},"updated_by_user":{"id":"72f64fa0-d865-42b4-b4b8-f0395f2cb2a7","first_name":"Daniel","last_name":"Emechete"}},{"id":31,"name":"CARTON ","code":"CAR","description":null,"is_active":true,"is_internal":false,"created_at":"2022-08-30T14:41:39.497Z","updated_at":"2022-08-30T14:41:39.497Z","created_by_user":{"id":"72f64fa0-d865-42b4-b4b8-f0395f2cb2a7","first_name":"Daniel","last_name":"Emechete"},"updated_by_user":null},{"id":30,"name":"VALUE","code":"VAL","description":null,"is_active":true,"is_internal":false,"created_at":"2022-07-08T14:25:50.226Z","updated_at":"2022-07-08T14:25:50.226Z","created_by_user":{"id":"6b2289e3-eecf-4b0c-9368-349fad976f77","first_name":"Onyema","last_name":"Ojukwu"},"updated_by_user":null},{"id":29,"name":"","code":"","description":null,"is_active":true,"is_internal":false,"created_at":"2022-06-28T13:16:39.987Z","updated_at":"2022-06-28T13:16:39.987Z","created_by_user":{"id":"6b2289e3-eecf-4b0c-9368-349fad976f77","first_name":"Onyema","last_name":"Ojukwu"},"updated_by_user":null},{"id":28,"name":"CONGO","code":"CON","description":"A unit of measurement for fruits and vegetables","is_active":true,"is_internal":false,"created_at":"2022-06-12T04:19:51.500Z","updated_at":"2022-06-12T04:20:07.238Z","created_by_user":null,"updated_by_user":{"id":"72f64fa0-d865-42b4-b4b8-f0395f2cb2a7","first_name":"Daniel","last_name":"Emechete"}},{"id":27,"name":"BUCKET","code":"BUC","description":"Unit of measurement for vegetables","is_active":true,"is_internal":false,"created_at":"2022-06-08T07:45:30.397Z","updated_at":"2022-06-08T07:45:57.711Z","created_by_user":null,"updated_by_user":{"id":"72f64fa0-d865-42b4-b4b8-f0395f2cb2a7","first_name":"Daniel","last_name":"Emechete"}},{"id":26,"name":"PAINT","code":"PAI","description":"Unit of measurement for fruits, vegetables and dry goods.","is_active":true,"is_internal":false,"created_at":"2022-06-02T07:32:25.982Z","updated_at":"2022-06-02T07:36:16.210Z","created_by_user":null,"updated_by_user":{"id":"72f64fa0-d865-42b4-b4b8-f0395f2cb2a7","first_name":"Daniel","last_name":"Emechete"}},{"id":22,"name":"TONNE","code":"TNE","description":"","is_active":true,"is_internal":false,"created_at":"2022-04-25T12:41:25.510Z","updated_at":"2022-04-25T12:42:45.570Z","created_by_user":null,"updated_by_user":null},{"id":21,"name":"SET","code":"SET","description":"","is_active":true,"is_internal":false,"created_at":"2022-04-25T12:41:14.569Z","updated_at":"2022-04-25T12:42:40.308Z","created_by_user":null,"updated_by_user":null},{"id":20,"name":"PEAK CUP","code":"PEA","description":"","is_active":true,"is_internal":false,"created_at":"2022-04-25T12:41:01.586Z","updated_at":"2022-04-25T12:42:36.643Z","created_by_user":null,"updated_by_user":null},{"id":19,"name":"BUNCH","code":"BCH","description":"","is_active":true,"is_internal":false,"created_at":"2022-04-25T12:40:25.024Z","updated_at":"2022-04-25T12:42:33.208Z","created_by_user":null,"updated_by_user":null},{"id":18,"name":"BASKET","code":"BSK","description":"","is_active":true,"is_internal":false,"created_at":"2022-04-25T12:40:01.834Z","updated_at":"2022-04-25T12:42:27.488Z","created_by_user":null,"updated_by_user":null},{"id":17,"name":"DOZEN","code":"DOZ","description":"","is_active":true,"is_internal":false,"created_at":"2022-04-25T12:39:43.729Z","updated_at":"2022-04-25T12:42:23.868Z","created_by_user":null,"updated_by_user":null},{"id":16,"name":"CUP","code":"CUP","description":"","is_active":true,"is_internal":false,"created_at":"2022-04-25T12:38:19.910Z","updated_at":"2022-04-25T12:42:21.299Z","created_by_user":null,"updated_by_user":null},{"id":15,"name":"ROLLS","code":"ROL","description":"","is_active":true,"is_internal":false,"created_at":"2022-04-25T12:38:03.360Z","updated_at":"2022-04-25T12:42:17.462Z","created_by_user":null,"updated_by_user":null},{"id":14,"name":"LITERS","code":"LIT","description":"","is_active":true,"is_internal":false,"created_at":"2022-04-25T12:37:34.226Z","updated_at":"2022-04-25T12:42:14.851Z","created_by_user":null,"updated_by_user":null},{"id":13,"name":"TONES","code":"TON","description":"","is_active":true,"is_internal":false,"created_at":"2022-04-25T12:37:06.491Z","updated_at":"2022-04-25T12:42:09.253Z","created_by_user":null,"updated_by_user":null},{"id":12,"name":"CRATE","code":"CR","description":"","is_active":true,"is_internal":false,"created_at":"2022-04-25T12:33:43.270Z","updated_at":"2022-04-25T12:42:03.044Z","created_by_user":null,"updated_by_user":null},{"id":11,"name":"DERICA","code":"DER","description":"","is_active":true,"is_internal":false,"created_at":"2022-04-25T12:32:45.339Z","updated_at":"2022-04-25T12:41:59.620Z","created_by_user":null,"updated_by_user":null},{"id":10,"name":"BUNDLE","code":"BD","description":"","is_active":true,"is_internal":false,"created_at":"2022-04-25T12:32:29.033Z","updated_at":"2022-04-25T12:41:57.216Z","created_by_user":null,"updated_by_user":null},{"id":9,"name":"MUDU","code":"MD","description":"uom for Dry goods","is_active":true,"is_internal":false,"created_at":"2022-04-25T12:31:50.967Z","updated_at":"2022-04-25T12:41:52.670Z","created_by_user":null,"updated_by_user":null},{"id":8,"name":"10 BAGS","code":"10BG","description":"","is_active":true,"is_internal":false,"created_at":"2022-04-25T12:31:28.852Z","updated_at":"2022-04-25T12:41:50.051Z","created_by_user":null,"updated_by_user":null},{"id":7,"name":"1 BAG","code":"1BG","description":"","is_active":true,"is_internal":false,"created_at":"2022-04-25T12:30:42.607Z","updated_at":"2022-04-25T12:41:45.459Z","created_by_user":null,"updated_by_user":null},{"id":6,"name":"PACK","code":"PAC","description":"","is_active":true,"is_internal":false,"created_at":"2022-04-25T12:29:44.779Z","updated_at":"2022-04-25T12:41:42.147Z","created_by_user":null,"updated_by_user":null},{"id":5,"name":"BAG","code":"BG","description":"Bag is a unit for measurement","is_active":true,"is_internal":false,"created_at":"2022-04-25T12:29:28.043Z","updated_at":"2022-04-25T12:41:39.816Z","created_by_user":null,"updated_by_user":null},{"id":4,"name":"BOTTLE","code":"BO","description":"Refers to a single instance of a bottled drink","is_active":true,"is_internal":false,"created_at":"2022-04-25T12:28:59.544Z","updated_at":"2022-04-25T12:41:36.400Z","created_by_user":null,"updated_by_user":null},{"id":3,"name":"EACH","code":"EA","description":"Refers to a single instance of a product","is_active":true,"is_internal":false,"created_at":"2022-04-25T12:28:35.479Z","updated_at":"2022-04-25T12:41:33.699Z","created_by_user":null,"updated_by_user":null},{"id":2,"name":"CARTON","code":"CA","description":"Unit of measurement for products packaged in cartons/bulk","is_active":true,"is_internal":false,"created_at":"2022-04-25T12:28:16.340Z","updated_at":"2022-04-25T12:41:31.472Z","created_by_user":null,"updated_by_user":null},{"id":1,"name":"KILOGRAM","code":"KG","description":"Unit of measurement for products described by weigh","is_active":true,"is_internal":false,"created_at":"2022-04-25T12:27:52.642Z","updated_at":"2022-04-25T12:27:56.922Z","created_by_user":null,"updated_by_user":null}]}


{"id":29006,"user_id":"02946396-6ffb-4ffa-8669-bc819e8a66b9","platform":"android","guest_id":null,"sub_total":"1002800","total_amount":"1002800","total_taxes":null,"vat":null,"vat_percent":null,"taxes":null,"delivery_fee":null,"branch_id":null,"created_at":"2023-11-30T10:26:55.773Z","updated_at":"2023-11-30T10:26:55.773Z","items":[{"id":147481,"product_id":7939,"name":"valentine chicken cut 4","comment":null,"uom":{"code":"KG","name":"KILOGRAM","image_url":"https://e-procurement-v2.s3.eu-west-3.amazonaws.com/images/product/1667230144854/Valentine%20Chicken%20Cut%204/153517/png","max_amount":2507,"uom_setup_id":"1","volume_range":[],"discount_type":"percent","discount_value":0,"supplier_price":2300,"vendease_price":2507,"is_quantity_range":false},"quantity":"400.0000","old_vendease_price":null,"total_amount":null,"total_tax":null,"vat_percent":null,"notified":false,"is_vatable":false,"amount":"1002800.0000","vat":null,"taxes":null,"discounted_price":null,"image_url":"https://e-procurement-v2.s3.eu-west-3.amazonaws.com/images/product/1667230144854/Valentine%20Chicken%20Cut%204/153517/png","description":"valentine chicken cut 4","country":{"id":1,"currency":"NGN"}}],"pay_now_amount":1002800,"pay_on_delivery_amount":1002800}

{"pay-later":null,"pay-on-delivery":null,"pay-now":null,"banned_data":null,"defaulting":{"status":true,"defaulted_invoices":[{"code":"I1701098764649","total_amount":"1122358.50"}],"total_amount":1122358.5}}