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
JessQA
Frequent Visitor

Looping (?) in Power BI

Hi Everyone,

 

I was hoping someone could help me with a part of the formula to get me to my end goal.

 

Basically, I want to create a new column in Power BI that says IF column D has "Rejected" then put "Rejected" for every Row after that in column E BUT the "loop" must end if there is a new ID Number. (Example Pictured).

 

Thank you!

 

Example 1.JPG

1 ACCEPTED SOLUTION
MarcelBeug
Community Champion
Community Champion

In the query editor (M / Power Query) you can achieve this without looping.

 

Steps to take:

Add 2 index columns: 1 starting with 0 and the other with 1 (Add Column - Index column).

Merge the table with itself such that you get the previous values for Fruit ID and Status on the same row as the current data

Sort on Index (as the merge disrupted the sort)

Add a custom column to determine the inital "Addition to Status": it will be "Rejected" if the previous status was "Rejected"and the Fruit Id is the same as the previous Fruit ID, otherwise if the fruit ID was changed it will be "" otherwise null.

Fill down this column, so "Rejected" will be filled down until the first non-null (= "" for each new fruit ID).

Remove columns that are no longer reuiqred.

 

The code below is illustrated with this video.

 

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Fruit ID Number", Int64.Type}, {"Fruit Status", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
    #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1),
    #"Merged Queries" = Table.NestedJoin(#"Added Index1",{"Index"},#"Added Index1",{"Index.1"},"NewColumn",JoinKind.LeftOuter),
    #"Renamed Columns" = Table.RenameColumns(#"Merged Queries",{{"NewColumn", "Previous"}}),
    #"Expanded Previous" = Table.ExpandTableColumn(#"Renamed Columns", "Previous", {"Fruit ID Number", "Fruit Status"}, {"Previous.Fruit ID Number", "Previous.Fruit Status"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Previous",{{"Index", Order.Ascending}}),
    #"Added Custom" = Table.AddColumn(#"Sorted Rows", "Addition to Status", each if [Previous.Fruit Status] = "Rejected" and [Previous.Fruit ID Number] = [Fruit ID Number] then "Rejected" else if [Fruit ID Number] <> [Previous.Fruit ID Number] then "" else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Addition to Status"}),
    #"Removed Columns" = Table.RemoveColumns(#"Filled Down",{"Index", "Index.1", "Previous.Fruit ID Number", "Previous.Fruit Status"})
in
    #"Removed Columns"

 

Specializing in Power Query Formula Language (M)

View solution in original post

8 REPLIES 8
MarcelBeug
Community Champion
Community Champion

Haha, if you mention the word "looping" you trigger the entire Quick Respons Team: "Thou shall not loop in Power BI" Smiley LOL

 

LOL

 

(By the way: looping is still possible in Power Query with List.Generate or with recursive functions, but in most cases looping can be avoided).

Specializing in Power Query Formula Language (M)

@MarcelBeug Just shows how flexible the environment is too with the different ways to achieve the same result. 🙂


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

MarcelBeug
Community Champion
Community Champion

In the query editor (M / Power Query) you can achieve this without looping.

 

Steps to take:

Add 2 index columns: 1 starting with 0 and the other with 1 (Add Column - Index column).

Merge the table with itself such that you get the previous values for Fruit ID and Status on the same row as the current data

Sort on Index (as the merge disrupted the sort)

Add a custom column to determine the inital "Addition to Status": it will be "Rejected" if the previous status was "Rejected"and the Fruit Id is the same as the previous Fruit ID, otherwise if the fruit ID was changed it will be "" otherwise null.

Fill down this column, so "Rejected" will be filled down until the first non-null (= "" for each new fruit ID).

Remove columns that are no longer reuiqred.

 

The code below is illustrated with this video.

 

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Fruit ID Number", Int64.Type}, {"Fruit Status", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
    #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1),
    #"Merged Queries" = Table.NestedJoin(#"Added Index1",{"Index"},#"Added Index1",{"Index.1"},"NewColumn",JoinKind.LeftOuter),
    #"Renamed Columns" = Table.RenameColumns(#"Merged Queries",{{"NewColumn", "Previous"}}),
    #"Expanded Previous" = Table.ExpandTableColumn(#"Renamed Columns", "Previous", {"Fruit ID Number", "Fruit Status"}, {"Previous.Fruit ID Number", "Previous.Fruit Status"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Previous",{{"Index", Order.Ascending}}),
    #"Added Custom" = Table.AddColumn(#"Sorted Rows", "Addition to Status", each if [Previous.Fruit Status] = "Rejected" and [Previous.Fruit ID Number] = [Fruit ID Number] then "Rejected" else if [Fruit ID Number] <> [Previous.Fruit ID Number] then "" else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Addition to Status"}),
    #"Removed Columns" = Table.RemoveColumns(#"Filled Down",{"Index", "Index.1", "Previous.Fruit ID Number", "Previous.Fruit Status"})
in
    #"Removed Columns"

 

Specializing in Power Query Formula Language (M)

Thank you so much this was perfect!

Vvelarde
Community Champion
Community Champion

@JessQA

 

hi, try with this new calculated column (using DAX)

 

Addition to Status =
VAR Rejected =
    CALCULATE (
        VALUES ( Table1[Time] )
        ALLEXCEPT ( Table1, Table1[FruitID Number] ),
        Table1[Fruit Status] = "Rejected"
    )
VAR RejectedNumber = Table1[FruitID Number]
RETURN
    IF (
        Rejected <> BLANK (),
        IF (
            AND ( Table1[FruitID Number] = RejectedNumber, Table1[Time] > Rejected ),
            "Rejected"
        )
    )



Lima - Peru
Anonymous
Not applicable

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTLUM9QzMjA0BzL98ksUgjILUpVidSByRgi5ImRxY4R4QGZydmoKXMYEIROUmpWaXIIkh8smIxw2GaHahGKeEapdmXkKbolFuQrBJflFiekI7bisNMZhpTFOzxkTttAYj4UmOCw0wWmhCQELYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Fruit ID" = _t, Date = _t, #"Fruit Status" = _t]),
    AddedIndex = Table.AddIndexColumn(Source, "Index", 1, 1),
    AddedColumn1 = Table.AddColumn(AddedIndex, "Column1", each  try
if [Fruit ID] = AddedIndex{[Index]-2}[Fruit ID] and AddedIndex{[Index]-2}[Fruit Status] = "Rejected" then 
"Rejected" else null
otherwise null),
    AddedIndex2 = Table.AddIndexColumn(AddedColumn1, "Index2", 1, 1),
    AddedColumn2 = Table.AddColumn(AddedIndex2, "Column2", each try
if [Fruit ID] = AddedIndex2{[Index2]-2}[Fruit ID] and AddedIndex2{[Index2]-2}[Column1] = "Rejected" then "Rejected" else null
otherwise null),
    Added_AdditionToStatus = Table.AddColumn(AddedColumn2, "AdditionToStatus", each if [Column1] <> null then [Column1] else [Column2]),
    RemoveOtherColumns = Table.SelectColumns(Added_AdditionToStatus,{"Fruit ID", "Date", "Fruit Status", "AdditionToStatus"})
in
    RemoveOtherColumns

Hi @JessQAMaybe not an elegant solutions, but it works.

Maybe @ImkeF can do it better (Function?)?

Phil_Seamark
Employee
Employee

Please try this if you are keen to try a DAX solution.  Just add a new table and use this code.

 

I've assumed your raw datatable is called FruitData

 

New Table = 
VAR T1 = SELECTCOLUMNS(FILTER('FruitData','FruitData'[Fruit Status]="Rejected"),
    "Fruit ID Number2",[Fruit ID Number],
    "Time2",[Time])
VAR T2 = SELECTCOLUMNS(
                    CROSSJOIN(FruitData,T1),
                    "Fruit ID Number",[Fruit ID Number] , 
                    "Time" , [Time] , 
                    "Fruit Status" , [Fruit Status] , 
                    "Addition to Status" , IF(
                                        [Fruit ID Number]=[Fruit ID Number2] && 
                                        [Time] >= [Time2],
                                        "Rejected",
                                        Blank()
                                       )
                               )
RETURN T2

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

lloydz1
New Member

This is probably not the answer you are looking for but simply how I would approach it. 

 

I would create a seperate data table with the id and status where the 'Fruit Status' is rejected

rename 'Fruit Status' in this table to 'Adition to Status'

then simply merge them together

 

if you dont want an 'Addition to Status' of rejected, when the 'Fruit Status' is rejected, and where 'Adition to Status' = 'Fruit Status' set the fruit status to blank.

 

Hope this is helps.

 

If your data source is SQL i would recommend doing that in SQL it would be probably be easier and quicker 

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.