Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I need to fabricate data in a table, so I created a number.randombetween() custom column in Power Query, which gives me what I need. However, doing anything after creates duplicate values. I'd like to lock down the initial values and turn them into a static column.
Is there a Power Query equivalent of Excel's "take only the values and ignore the source formula" to achieve that?
As a workarand 😁, try one of these (duplicate the query to see how it works) :
let
tab=Table.FromList(
{"gen",
"feb",
"mar",
"apr",
"mag",
"giu",
"lug",
"ago",
"set",
"ott",
"nov",
"dic",
"gen",
"feb",
"mar",
"apr",
"mag",
"giu",
"lug",
"ago",
"set",
"ott",
"nov",
"dic"
},null,{"Column1"}),
Origine = tab,
#"Aggiunta colonna indice" = Table.AddIndexColumn(Origine, "Indice", 1, 1),
#"Aggiunta colonna personalizzata" = Table.AddColumn(#"Aggiunta colonna indice", "RandNoSeed", each List.Random(1){0}),
#"Aggiunta colonna personalizzata1" = Table.AddColumn(#"Aggiunta colonna personalizzata", "RandWithRowSeed", each List.Random(1,[Indice]){0}),
#"Aggiunta colonna personalizzata2" = Table.AddColumn(#"Aggiunta colonna personalizzata1", "RandWithTabSeed", each List.Random(1,1){0})
in
#"Aggiunta colonna personalizzata2"
Here a function to call to get rando number between low and high
let FrozendRandBetween=(low as number, high as number, optional seed) =>
let
FrRndBtw=low+List.Random(1,seed){0}*(high-low)
in
FrRndBtw
in
FrozendRandBetween
here some example on how to call the function:
List.Accumulate({1..20},{},(s,c)=> s&{Number.Round(#"RandBetween"(3, 7,1))})
List.Accumulate({1..20},{},(s,c)=> s&{Number.Round(RandBetween(3, 7,c))})
let
getFreqListElement= ( lista as list) =>
let
TextList=List.Transform(lista, each Text.From(_)),
piuno=(n) => [ n=n+1] [n],
freq=List.Accumulate(TextList,[],(s,c)=>if Record.HasFields(s,c) then Record.TransformFields(s, {c,piuno}) else Record.AddField(s,c,1))
in
freq
in
getFreqListElement
Bump - tried a few things, but the table's too large to export to excel (so I can't add a RANDBETWEEN and reimport), and I can't add it to the source.
Add as New Query, or any other step, makes all values identical. This was initially solved with an index, but I can't figure it out now.
Hi @OwenAuger,
Both steps were unsuccessful. Below is the table before any addtional steps. All I need is for Plan_Fab to become static.
Table.Buffer gives me the following:
The first index column was needed to get randbetween to work in the first place. Adding a second index column refreshes the data, which is what I'm trying to avoid:
Hmm...
Just re-confirming, your original issue was that you were getting the same random number repeated on every row wasn't it?
Not sure why Table.Buffer returned a text value rather than the buffered table. Could you post your complete M code?
Also, as far as I know there's no way of preventing the random numbers changing when a preview refresh is triggered. i.e. there's no way to save a particular instance of the random numbers while in the Power Query editor. But Once the table is loaded to the data model, it should be stable until the next data model refresh.
I'll just call in some M specialists to see if they can help @ImkeF @MarcelBeug ?
Regards,
Owen
Can confirim @OwenAuger s statements: Thats all you can do today. There is no way to freeze the random numbers in the query editor.
So a workaround could be to export to csv using R and then re-import or use R to generate the random numbers itself.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |