Reply
Frequent Visitor
Posts: 3
Registered: ‎02-15-2017
Accepted Solution

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


Accepted Solutions
Highlighted
Super Contributor
Posts: 866
Registered: ‎11-25-2016

Re: Looping (?) in Power BI

[ Edited ]

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"

 

View solution in original post


All Replies
Frequent Visitor
Posts: 2
Registered: ‎03-06-2017

Re: Looping (?) in Power BI

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 

Super Contributor
Posts: 1,441
Registered: ‎11-29-2015

Re: Looping (?) in Power BI

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
Established Member
Posts: 148
Registered: ‎08-24-2015

Re: Looping (?) in Power BI

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?)?

Super Contributor
Posts: 1,190
Registered: ‎05-10-2016

Re: Looping (?) in Power BI

@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"
        )
    )
Highlighted
Super Contributor
Posts: 866
Registered: ‎11-25-2016

Re: Looping (?) in Power BI

[ Edited ]

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"

 

Super Contributor
Posts: 866
Registered: ‎11-25-2016

Re: Looping (?) in Power BI

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

Super Contributor
Posts: 1,441
Registered: ‎11-29-2015

Re: Looping (?) in Power BI

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

Frequent Visitor
Posts: 3
Registered: ‎02-15-2017

Re: Looping (?) in Power BI

Thank you so much this was perfect!