Forum Discussion

Edderlyna's avatar
Edderlyna
New Member
3 years ago
Solved

Expression Error: The name ‘List.Split wasn’t recognised.

Hi, I am a new to Power Query. I'm using Excel 2016. 

 

Last week when I using the function 'List.Split', it work's totally fine, but now when I refresh my query I get this error (Expression error: The name 'List.Split' wasn't recognized. Make sure it's spelled correctly.)

 

I have checked multiple times and re-do steps by steps according a learning video but can't spot which step I get it wrong. The List.Split function just do not works on my computer.

 

I'm trying to convert multiple column groups to rows. From the left table and expecting a result as right table.

 

Column1Column2Column3Column4Column5Column6 CityNameAge
Mumbai New York Hong Kong  MumbaiA14
NameAgeNameAgeNameAge MumbaiB12
A14E14I15 MumbaiC18
B12F12J17 MumbaiD10
C18G18K15 New YorkE14
D10  L12 New YorkF12
    M13 New YorkG18
       Hong KongI15
       Hong KongJ17
       Hong KongK15
       Hong KongL12
       Hong KongM13


Was stuck at the step as below

 

let
  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
  Sheet1_Sheet = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type any}, {"Column3", type text}, {"Column4", type any}, {"Column5", type text}, {"Column6", type any}}),
  Cols = List.Distinct(Record.ToList(Sheet1_Sheet{1})),
  ToLists = List.Transform(Table.ToColumns(Table.Skip(Sheet1_Sheet,2)),each List.RemoveNulls(_)),
  Custom1 = List.Split(ToLists,2)
in
  Custom1

Did this function still works or any update done and it has been removed?
If the List.Split function is not functioning, any other way I can do to get the similar results? 

Thank you in advanced for any one read my question.
Need Help
  • jbwtp's avatar
    jbwtp
    3 years ago

    Hi Edderlyna,

     

    This is quite strange... Can you try using the List.Split in a different environment? E.g. in Excel if you do it in PBI and vise versa?

     

    The List.Split can be replaced with something like this:

    fListSplit = (list, pageSize) => List.Accumulate({0..Number.RoundUp(List.Count(list) / pageSize) - 1}, {}, (a, n)=> a & {List.Range(list,  n * pageSize, pageSize)})

    And then you just call it fListSplit rather than List.Split. It will do the same thing. But this is quite strange. List.Split works Ok in my PQ.

     

    Cheers,

    John

3 Replies

  • jbwtp's avatar
    jbwtp
    Memorable Member
    3 years ago

    Hi Edderlyna,

     

    This is quite strange... Can you try using the List.Split in a different environment? E.g. in Excel if you do it in PBI and vise versa?

     

    The List.Split can be replaced with something like this:

    fListSplit = (list, pageSize) => List.Accumulate({0..Number.RoundUp(List.Count(list) / pageSize) - 1}, {}, (a, n)=> a & {List.Range(list,  n * pageSize, pageSize)})

    And then you just call it fListSplit rather than List.Split. It will do the same thing. But this is quite strange. List.Split works Ok in my PQ.

     

    Cheers,

    John

    • Edderlyna's avatar
      Edderlyna
      New Member
      3 years ago

      Hi jbwtp John,

      I tried List.Split on my office computer and it works OK. Might be my excel version wasn't updated. Thank you for your time to provide advice and solutions. I tried on the fListSplit. It's quite abstruse to me , will find out myself about it. Thank you again! 🙂🙂

  • tekhnikos's avatar
    tekhnikos
    New Member
    3 years ago

    I am also trying to use List.Split and am receiving the same message.  Adding to this thread for visibility