Unable to persist random values in M Language

[PaulCooper]

I am trying to add unit testing to my Power BI Connector project using the unit testing method provided by the https://github.com/Microsoft/DataConnectors/tree/master/samples/UnitTesting sample. This has been working out fine for most tests until I tried writing a column selector test (onSelectColumn).

As there are ~150 tables (REST API resources) accessable from my connector some with hundreds of fields so I thought it might be a good idea to use sets of random columns based on the schema and if there are errors add the list of columns to the notes in the test results. I was able to create the list of columns (on a per table basis) using the following command:

RandomColsPerEntity = List.Transform({SchemaTableToRecords{0}}, each

[Entity = [Entity], Columns = List.Transform(List.Select(Table.ToRecords(Type.TableSchema([Type])), each Number.Random() < 0.2), each [Name])]

)

SchemaTableToRecords is the schema table converted to a list of records using Table.ToRecords

I found that I was unable to persist the random columns. So the following printed different sets of columns:

Fact("Columns: " & (Text.FromBinary(Json.FromValue(RandomColsPerEntity))), true, true),
Fact("Columns: " & (Text.FromBinary(Json.FromValue(RandomColsPerEntity))), true, true),

It looks like each time RandomColsPerEntity is referenced it is re-calculated.

I have looked around for a way to make the data persistent but nothing seems to work. I have tried to see if there was a way to save the data to a file and then just keep reading that back, but the M language doesn't support this.

Is there anything I can do to make random created values not be re-calculated each time?

With Kind Regards,

Paul Cooper

[rcyber]

Hello @PaulCooper 

Have you found a solution for this issue?

I need to do something like that

Regards,

Alex

[PaulCooper]

Sorry for the delay in responding.

I have solved it but not via M. I already use a Node.js script to write M code for the schema in to my Connector project. So I extended this to replace a placehoder in the unit testing file with a random intial seed (using Math.random()). I then use a simple function that uses a Linear Congruential pseudo-random number generator algorithm (See https://en.wikipedia.org/wiki/Linear_congruential_generator) to generate the next number from an input seed value and fixed values for the Multiplier, Increment and Modulo. (103867, 13751 and 65536 respectively. Note that the first two are primes and the Modulo is a power of 2). This output number is divided by the Modulo to give a decimal number between 0 to 1. This way I get deterministic random numbers. Please note the initial seed should be an integer between 0 and the Modulo - 1.

I use the initial seed added to an index number of entries in an array to create the input seed I pass to the function to create a list of random numbers between 0 and 1. I apply the test of "<0.5" to these numbers to choose which entries of the array I use. (Yes, I did use 0.2 in my original post.)

Every time the code is built a new initial seed is put in the unit test file and as the M code is deterministic if a test fails then it can report the values actually used.

[rcyber · Accepted solution]

Hello @PaulCooper ,

That's good solution but I have found a workaround already.

Could you please explain this one:

---

@PaulCooper wrote:

I already use a Node.js script to write M code for the schema in to my Connector project.

---

How did you implement that? I'll be very appreciated for any info about that.