Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Replace excel =if(...) with power query (based on former row)

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:

AB
00
101
01
102
102
02
02
103
103

 

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

2 ACCEPTED SOLUTIONS

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

View solution in original post

v-alq-msft
Community Support
Community Support

Hi, @Anonymous 

 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

c1.png

 

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:

c2.png

 

Best Regards

Allan

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
v-alq-msft
Community Support
Community Support

Hi, @Anonymous 

 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

c1.png

 

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:

c2.png

 

Best Regards

Allan

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

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

Anonymous
Not applicable

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

ziying35
Impactful Individual
Impactful Individual

@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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors