Forum Discussion

nitingupta_cloud's avatar
nitingupta_cloud
Copper Contributor
Apr 28, 2021

Excel power query split column by delimiter into rows missing

Hi All,

I am using MS Office professional 2016. While using Power query in Excel, i couldn't see the option split column by delimiter into rows in advance options. i could only see delimiter by columns. can anyone help me out in this please? i have already upgraded office to latest build.

excel

3 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Apr 28, 2021

    nitingupta_cloud 

    There is no setting which could change such behavior, afraid PQ for 2016 is not updated with new functionality.

    You may try to add custom column with Text.Spit([ColumnName], ",") and expand received list into rows.

    • nitingupta_cloud's avatar
      nitingupta_cloud
      Copper Contributor
      Apr 30, 2021

      SergeiBaklan HI Sergei, thanks for replying. The weird thing is i have another laptop in which the same version of office is installed Office professional 2016 and that has the option split into rows in advance options.

       

       

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Apr 30, 2021

        nitingupta_cloud 

        As I remember with 2016 Power Query for Pro and not Pro versions have different functionality, perhaps that's the reason. You may compare versions in query settings, Diagnostic tab.

Resources