Randomly assiginng a specific value to one of the rows

[smpa01]

Hi,

I am trying to achieve a task which involves randomly assigining value "2" to one of the rows only. Is it possible to achieve in PBI/Power Query (DAX- not preferred). I have a column called "E Address". For each of the "E Address" I want PBI to assign a 2 to only one of the rows randomly and rest of the rows would be 1.

 E AddressRandom Sample 

abc@example.com	1
efg@example.com	1
hij@example.com	1
jkl@example.com	2

Thank you in advance

[Zubair_Muhammad]

Hi @smpa01

First add a column as follows

RandomNumber = RANDBETWEEN(1,100000)/100

 Now add another column to get your desired assigned values

Assigned Value =
IF ( TableName[RandomNumber] = MAX ( TableName[RandomNumber] ), 2, 1 )

[Zubair_Muhammad]

@smpa01

[smpa01]

Hi Zubair,

Thanks for your post. Is there way to achieve the result by not using DAX at all?

[Zubair_Muhammad]

Hi @smpa01

I think there should be a way using QUERY EDITOR as well....

But I am not aware of it 

[smpa01 · Accepted solution]

@Zubair_MuhammadThanks for you solution. I picked up cues from your post and another post - http://community.powerbi.com/t5/Desktop/Custom-Column-Randbetween/m-p/216944#M96082

The result is I managed to solve it the way I wanted it. Here is the code.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Email", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each 1),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Number.RandomBetween(1+[Index]-[Index],100000)),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Custom.1", Int64.Type}}),
    #"Inserted Division" = Table.AddColumn(#"Changed Type1", "Division", each [Custom.1] / 100, type number),
    #"Grouped Rows" = Table.Group(#"Inserted Division", {"Custom"}, {{"Max", each List.Max([Division]), type number}, {"AD", each _, type table}}),
    #"Expanded AD" = Table.ExpandTableColumn(#"Grouped Rows", "AD", {"Email", "Index", "Division"}, {"AD.Email", "AD.Index", "AD.Division"}),
    #"Added Custom2" = Table.AddColumn(#"Expanded AD", "Custom.1", each if [AD.Division]=[Max] then 2 else 1)
in
    #"Added Custom2"

Raw Data - https://drive.google.com/open?id=1osjur8vQtLnW5UL4_E-17D2FKFpmsPjQj2_v61rlPkk

Thanks @MarcelBeug

[smpa01]

Hi,

I am trying to achieve a task which involves randomly assigining value "2" to one of the rows only. Is it possible to achieve in PBI/Power Query (DAX- not preferred). I have a column called "E Address". For each of the "E Address" I want PBI to assign a 2 to only one of the rows randomly and rest of the rows would be 1.

E AddressRandom Sample 

abc@example.com	1
efg@example.com	1
hij@example.com	1
jkl@example.com	2

Thank you in advance.