Forum Discussion

ronaldbalza2023's avatar
ronaldbalza2023
Icon for Continued Contributor rankContinued Contributor
5 years ago
Solved

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, ...
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-jingzhang's avatar
    v-jingzhang
    5 years ago

    Hi ronaldbalza2023 

     

    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.

v-jingzhang's avatar
v-jingzhang
Icon for Community Support rankCommunity Support
5 years ago

Hi ronaldbalza2023 

 

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.