Table.FromList.

dicken

Active Member
Joined
Feb 12, 2022
Messages
292
Office Version
  1. 365
Platform
  1. Windows
Hi,
In power query when using the Table.FromList function, I add ; Splitter.SplitByNothing()
after the source list, as it doesn't seem to work if you don't . It says in microsoft docs that the splitter is optional, I tried
just putting the value null, but this doesn't work either, can someone explain what this actually does.

Richard.
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
It looks to me that Table.FromList expects a list of text values from which it builds the rows of the table by doing a splitting operation on each text value in the list. Its default is to split by comma if no splitter function is specified. Paste the following into the advanced editor and click each step to see some examples. As you can see in the tbl3 step, the list must be arranged so the largest number of columns is in the lead row if rows may have differing number of column. It gives the columns generic Column1, Column2 ... names unless you supply a list of column names as in step tbl6

Power Query:
let
    tbl = Table.FromList({"A,B,C","D,E,F"}),
    tbl0 = Table.FromList({"A,B,C","D,E,F"},Splitter.SplitByNothing()),
    tbl1 = Table.FromList({"A B C","D E F"}),
    tbl11 = Table.FromList({"A;B;C","D;E;F"}),
    tbl2 = Table.FromList({"A B C","D E F"},Splitter.SplitTextByDelimiter(" ")),
    tbl3 = Table.FromList({"A,B,C","D,E", "F,G,H,I"}),
    tbl4 = Table.FromList({"A,B,C,D","E,F,G", "H,I"}),
    tbl5 = Table.FromList({"ABCD","EFG","HI"}, Splitter.SplitTextByRepeatedLengths(1)),
    tbl6 = Table.FromList({"110100100010000"}, Splitter.SplitTextByPositions({0,1,3,6,10}),{"One", "Ten", "Hundred", "Thousand", "Ten Thousand"})
in
    tbl6
 
Upvote 0
Thanks, two questions, in example 3 , you haven't got a splitter, this started when using List.Generate to create a date table.
and thought that instead of adding a step ,converting to table. I'd just wrap List.FromTable around it, but this always seem to insist
i add the splitter, and just wondered why ?
I like the last by position example I shall play around with that.
Lastly do you know what the other arguments are for, "column as any, default as any " ?

Richard
 
Upvote 0
Column as any argument is where to specify the column names as I did in step tbl6.

I'm not sure what the default as any argument does.

One more argument is ExtraValues which can error handle the situation in example in tbl3
 
Upvote 0
Column as any argument is where to specify the column names as I did in step tbl6.

I'm not sure what the default as any argument does.

One more argument is ExtraValues which can error handle the situation in example in tbl3
Thank you,
RD
 
Upvote 0

Forum statistics

Threads
1,224,454
Messages
6,178,766
Members
452,875
Latest member
Disastrouscoder

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top