Add column, fill with 3 random values only

[JackDanIce]

Hi,

I need to add a column to my table which contains values "Met", "Partially met" and "Not met" but I can't figure out how to do this.

I've tried to add a column and fill with RANDBETWEEN(1,3), but it returns a mixed number, but each row contains the same random value, e.g. 1.34567

Unable to find an answer online, can anyone suggest how to add the 3 values above as a new column in random order?

TIA,
Jack

 

[lrobbo314]

You need an Index column for RandomBetween to work correctly. Here is an example of what you are talking about doing.

Book3
	A	B	C	D	E
1	Column1			Column1	Value
2	a,b,c,d			a,b,c,d	Not Met
3	e,f,g,h			e,f,g,h	Not Met
4	I,j			k	Met
5	k			I,j	Partially Met
6	l			l	Partially Met
7	m			m	Met
8	n,o,p,z			n,o,p,z	Met
Sheet4

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    AddedIndex = Table.AddIndexColumn(Type, "Index", 0, 1, Int64.Type),
    Random = Table.AddColumn(AddedIndex, "Custom", each Int64.From(Number.RandomBetween(1,3))),
    Lookup = Table.FromRecords({[Number=1,Value="Met"],[Number=2,Value="Partially Met"],[Number=3,Value="Not Met"]}),
    Merge = Table.NestedJoin(Random, {"Custom"},Lookup, {"Number"}, "Lookup", JoinKind.LeftOuter),
    Expand = Table.ExpandTableColumn(Merge, "Lookup", {"Value"}, {"Value"}),
    ROC = Table.SelectColumns(Expand,{"Column1","Value"})
in
    ROC

 

[JackDanIce]

Hi @lrobbo314, thanks for replying I'll try to adapt this tomorrow and post back with efforts

 

[JackDanIce]

Thank you, I got it to work

 

[lrobbo314]

Right on!

 

[lrobbo314]

Here is another way to do it without needing to add and remove an index column.

Book1
	A	B
1	Column1	Custom
2	a,b,c,d	Met
3	e,f,g,h	Partially Met
4	I,j	Not Met
5	k	Partially Met
6	l	Partially Met
7	m	Met
8	n,o,p,z	Not Met
Table5

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    RB = Table.AddColumn(Source, "Custom", each 
        let 
            input = Number.Round(Number.RandomBetween(1,3)),
            vals = List.Zip({{1,2,3},{"Met","Partially Met", "Not Met"}}),
            Result = List.First(List.Select(vals, each _{0}=input)){1}
        in
            Result
    )
in
    RB