Forum Discussion

Betty888's avatar
Betty888
Icon for Helper II rankHelper II
3 years ago
Solved

Table.group not working because of null values

Dear all,

I’m trying to group data based on merged column that I was able to handle successfully until this last step of “group by” where I get the following error message saying that a null value can't be converted to Text :

I tried to come back to all the columns involved in the previous steps to replace null by “ “ (space) but I always receive this message.

My last tested solution was to use the following query at the beginning:

= Table.ReplaceValue(#"Previous step",null," ",Replacer.ReplaceValue, Table.ColumnNames (#"Previous step"))

but I still have some null values that create this error.

Can anyone help with any idea?

Many thanks in advance 🙏

Regards,  

Need Help
Tips and Tricks
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    3 years ago

    Hi, Betty888 ;

    In Power Query, if a column value is a text type, empty will be displayed as " ", when the column value is a numeric type, empty will be displayed as null, according to your prompt, the number type of your column should be a number type rather than a text type, so you can't perform your operation, you can convert to text type first, and then operate.


    Best Regards,
    Community Support Team _ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • BA_Pete's avatar
    BA_Pete
    Icon for Super User rankSuper User
    3 years ago

    Hi Betty888 ,

     

    This doesn't look like a specific Group By issue. Group By doesn't generally care what your data types are, unless you're declaring them in one of the arguments.

    Can you paste your query into a code window ( </> button ) here please? You can anonymise the source connection using XXX's if you want, but make sure to leave the connection function ( sql.database(...) etc.) in place.

     

    Thanks,

     

    Pete

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi, Betty888 ;

    In Power Query, if a column value is a text type, empty will be displayed as " ", when the column value is a numeric type, empty will be displayed as null, according to your prompt, the number type of your column should be a number type rather than a text type, so you can't perform your operation, you can convert to text type first, and then operate.


    Best Regards,
    Community Support Team _ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.