Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Mubar22
New Member

Power Query Split by Column Each Occurrence giving error

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.

Mubar22_0-1705160385821.pngMubar22_1-1705160431266.png

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

Mubar22_2-1705160557069.png

 

Thanks,

Mubar

1 ACCEPTED SOLUTION

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

Mubar_0-1705303512309.png

Thanks

View solution in original post

8 REPLIES 8
serpiva64
Super User
Super User

Hi,

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

serpiva64_1-1705252206816.png

 

 

serpiva64_0-1705252159081.png

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

 

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

Mubar_0-1705253732559.png

I only have 2 columns splitted

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.

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

Mubar_0-1705303512309.png

Thanks

serpiva64
Super User
Super User

Hi,

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

Here is content in the rows

Mubar_1-1705167468903.png

 

Mubar_0-1705167461534.png

 

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)  

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}}

 

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors