Lists of text and number types

[Dicken]

Hi, 
can someoen explain this ; if you have a llsit 

 = let alist = 
 {1,"a","b",3,3} in alist

you get a list of text and number alignment's left and right , but if you have a nested list 

= let alist = 
{ {"a",1} , {"b",2} , {"c", 3}} 
in alist

then all is left or text aligned,   any ideas as to what happens between one and nested to cause this? 

Richard

[AmiraBedh · Accepted solution]

Hello Dicken !

What you’re seeing is just the PQ UI is reacting to types.

{1,"a","b",3,3} each element is a scalar value and PQ knows the type of each item and the preview grid aligns them accordingly: numbers right, text left.

{{"a",1},{"b",2},{"c",3}} here each element of the outer list is itself a list and in the preview, PQ doesn’t look inside those inner lists when they decide alignment so it just shows values which are aligned like text. If you expand them, the columns start as type any which also aligns left until you set a numeric type.

So if you want the proper numeric right alignment, turn that nested list into a real table and set types:

let
    alist   = { {"a",1}, {"b",2}, {"c",3} },
    asTable = Table.FromRows(alist, {"Letter","Number"}),
    typed   = Table.TransformColumnTypes(asTable, {{"Letter", type text}, {"Number", Int64.Type}})
in
    typed

[vojtechsima · Accepted solution]

Hey, @Dicken ,

Lists are lazily evaluated. On demand, Power Query evaluates the top-level and structure. If you would nest the same list as in your first example:

let alist = {
 {1,"a","b",3,3}} in alist

You'll get the same behaviour, everything as text:

Once you go inside and inspect each value separately, you'll see the types evaluated precisely:

I general, Power Query wants to be as lazy as possible, if it doesn'T have to evaluate something, it won'T, so unless you call the concrete item in a list (for example by displaying the lowest nested items), it won't do the types as you would expect.

More on evaluation in my blog:

https://www.vojtechsima.com/post/how-power-query-m-evaluates-your-queries-and-why-the-performance-ge...

[vojtechsima · Accepted solution]

Hey, @Dicken ,

Lists are lazily evaluated. On demand, Power Query evaluates the top-level and structure. If you would nest the same list as in your first example:

let alist = {
 {1,"a","b",3,3}} in alist

You'll get the same behaviour, everything as text:

Once you go inside and inspect each value separately, you'll see the types evaluated precisely:

I general, Power Query wants to be as lazy as possible, if it doesn'T have to evaluate something, it won'T, so unless you call the concrete item in a list (for example by displaying the lowest nested items), it won't do the types as you would expect.

More on evaluation in my blog:

https://www.vojtechsima.com/post/how-power-query-m-evaluates-your-queries-and-why-the-performance-ge...

[Dicken]

Thanks to both, I have a look at the blog, so let me see if i get this,   as 
lists are nested,     {  {} }   the first level is a list not a number which affects subsequent values ? 

[vojtechsima · Accepted solution]

@Dicken 
Yeah, basically. Until you evaluate a specific item from the nested list, you MAY not get the type. The may is important, as it's kinda specific to lists, as you can see it does differentiate between the types, and for exampl,e tables preview will show you the correct representation immediately.

See example:

= {
#table({"col1", "col2", "col3"}, {{"a", "b", 1}}), {1,2,3, "1", "a"}
}

[Dicken]

thanks for the help.

[AmiraBedh · Accepted solution]

Hello Dicken !

What you’re seeing is just the PQ UI is reacting to types.

{1,"a","b",3,3} each element is a scalar value and PQ knows the type of each item and the preview grid aligns them accordingly: numbers right, text left.

{{"a",1},{"b",2},{"c",3}} here each element of the outer list is itself a list and in the preview, PQ doesn’t look inside those inner lists when they decide alignment so it just shows values which are aligned like text. If you expand them, the columns start as type any which also aligns left until you set a numeric type.

So if you want the proper numeric right alignment, turn that nested list into a real table and set types:

let
    alist   = { {"a",1}, {"b",2}, {"c",3} },
    asTable = Table.FromRows(alist, {"Letter","Number"}),
    typed   = Table.TransformColumnTypes(asTable, {{"Letter", type text}, {"Number", Int64.Type}})
in
    typed