Forum Discussion
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!
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"
8 Replies
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"Thank you so much this was perfect!
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 T2let 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 RemoveOtherColumnsHi JessQAMaybe not an elegant solutions, but it works.
Maybe ImkeF can do it better (Function?)?
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" ) )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
Haha, if you mention the word "looping" you trigger the entire Quick Respons Team: "Thou shall not loop in Power BI" :smileylol:
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).
MarcelBeug Just shows how flexible the environment is too with the different ways to achieve the same result. :)
