Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
Have a big production data set with 200k+ rows and growing
I want to make a "index" for eatch time a value in column A gets over 9, and with the result in column B:
A | B |
0 | 0 |
10 | 1 |
0 | 1 |
10 | 2 |
10 | 2 |
0 | 2 |
0 | 2 |
10 | 3 |
10 | 3 |
Today i use power query to make a conditional column: if value in A bigger than 9 set value in conditional column to 1
and:
in excel: If('conditioncal column'2 >'conditional column'1;B1+1;B1)
Have tried: https://community.powerbi.com/t5/Desktop/Adding-conditional-index-based-on-changing-field-in-Power-Q...
But that takes for ever to load (never managed to end the calucaltion)
Any good ideas?
Thorstein
Solved! Go to Solution.
Hi @Anonymous ,
if your index should only increment by 1, I'd recommend using List.Generate for it like so:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlDSAeJYnWglQxDTEMxEsMCCRuhMbCywtDEKMxYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t, B = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"A", Int64.Type}, {"B", Int64.Type}}),
BufferedColumnA = List.Buffer(#"Changed Type"[A]),
Index = List.Generate( () =>
[CurrentItem = BufferedColumnA{0}, Index = 0, Counter = 1],
each [Counter] <= List.Count(BufferedColumnA),
each [
CurrentItem = BufferedColumnA{[Counter]},
DifferenceToPrevious = CurrentItem - [CurrentItem],
Index = if DifferenceToPrevious >= 10 then [Index] + 1 else [Index],
Counter = [Counter] + 1
],
each [Index]
),
AddWholeColumnToTable = Table.FromColumns( Table.ToColumns (#"Changed Type") & {Index}, Table.ColumnNames(#"Changed Type") & {"Index"})
in
AddWholeColumnToTable
Please paste the code into the advanced editor and follow the steps. It should be very fast.
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
Hi, @Anonymous
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
Table:
You may try the following codes in 'Advanced Editor'.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlCK1YlWMoRQKBwUMQyZWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"A", Int64.Type}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each let
index=[Index],
tab=Table.SelectRows(#"Added Index",each [Index]=index-1 and [A]<=9)
in
if Table.IsEmpty(tab)
then 0
else if [A]>9
then 1
else 0),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "B", each let index=[Index],custom=[Custom]
in
Table.RowCount(
Table.SelectRows(#"Added Custom",each [Index]<=index and [Custom]=1
)
)),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Index", "Custom"})
in
#"Removed Columns"
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Anonymous
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
Table:
You may try the following codes in 'Advanced Editor'.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlCK1YlWMoRQKBwUMQyZWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"A", Int64.Type}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each let
index=[Index],
tab=Table.SelectRows(#"Added Index",each [Index]=index-1 and [A]<=9)
in
if Table.IsEmpty(tab)
then 0
else if [A]>9
then 1
else 0),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "B", each let index=[Index],custom=[Custom]
in
Table.RowCount(
Table.SelectRows(#"Added Custom",each [Index]<=index and [Custom]=1
)
)),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Index", "Custom"})
in
#"Removed Columns"
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I think what you're trying to achieve would be better done with a calculated column in DAX rather than in Power Query. With a calculated column you're able to utilize row context and the EARLIER function, which you can use to store values.
Here's a few links to using the EARLIER function:
https://community.powerbi.com/t5/Desktop/Explanation-of-the-EARLIER-formula/td-p/529469
https://docs.microsoft.com/en-us/dax/earlier-function-dax
Hi @Anonymous ,
if your index should only increment by 1, I'd recommend using List.Generate for it like so:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlDSAeJYnWglQxDTEMxEsMCCRuhMbCywtDEKMxYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t, B = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"A", Int64.Type}, {"B", Int64.Type}}),
BufferedColumnA = List.Buffer(#"Changed Type"[A]),
Index = List.Generate( () =>
[CurrentItem = BufferedColumnA{0}, Index = 0, Counter = 1],
each [Counter] <= List.Count(BufferedColumnA),
each [
CurrentItem = BufferedColumnA{[Counter]},
DifferenceToPrevious = CurrentItem - [CurrentItem],
Index = if DifferenceToPrevious >= 10 then [Index] + 1 else [Index],
Counter = [Counter] + 1
],
each [Index]
),
AddWholeColumnToTable = Table.FromColumns( Table.ToColumns (#"Changed Type") & {Index}, Table.ColumnNames(#"Changed Type") & {"Index"})
in
AddWholeColumnToTable
Please paste the code into the advanced editor and follow the steps. It should be very fast.
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
Been a long time since i started this and took a "shortcut" with excel, which now is to slow (out of memory).
This is by far the fastest code for this. The other solutions would never end with 200k lines.
Had to rewright some and do it it twice to do what was with the real data, but works very well.
Thank you very much
@Anonymous
Does column A only vary between the numbers 0 and 10?Can you simulate a table that is close to your real data volume?
Attach excel formulas to some of the data if possible.
Upload the file to a cloud drive and then share the link here.