Forum Discussion
Adding new column / replace column in Power Bi
Hi everyone, I have a column with many of its rows that were empty. Is it possible to replace the whole column with a new one? I actually wanted to fill the empty rows with random Region. (NSW, ACT, NT, SA, WA ,etc.) I was able to do it in excel - generate random text based on a list but not in Power Bi. Thanks in advance.
Here's how I did it in excel
=CHOOSE(RANDBETWEEN(1,3),$K$4$,$K$5$,$K$6$)
You can get a similar result with Power Query. First store all possible regions in a separate table. Add an index column to this table. I name the table as "Regions".
In original table, add an index column first. This column will not be used, but without it the random column cannot be added correctly.
Then add a custom column using Int16.From(Number.RandomBetween(0, List.Max(Regions[Index]))). I name this column as RandomRegionIndex. This column has random numbers.
At last, merge Regions table to this table. Select RandomRegionIndex column in this table and Index column in Regions table to match. After merging, expand the table column and select only Region column to add to this table.
Reference: Random Number Generation in Power BI | Sandeep Pawar (pawarbi.github.io)
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
1 Reply
You can get a similar result with Power Query. First store all possible regions in a separate table. Add an index column to this table. I name the table as "Regions".
In original table, add an index column first. This column will not be used, but without it the random column cannot be added correctly.
Then add a custom column using Int16.From(Number.RandomBetween(0, List.Max(Regions[Index]))). I name this column as RandomRegionIndex. This column has random numbers.
At last, merge Regions table to this table. Select RandomRegionIndex column in this table and Index column in Regions table to match. After merging, expand the table column and select only Region column to add to this table.
Reference: Random Number Generation in Power BI | Sandeep Pawar (pawarbi.github.io)
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.