Forum Discussion
Solved
Iteration with conditions
Hi,
Is it possible to iterate through rows in PowerBi in such way that:
1. First number > 0 is assigned "START" (not 2nd, 3rd etc) then, first number (after first condition) = 0 is assigned END (see table belowe and output needed)
2. If only one number > 0 -> START/END
Table:
and output I need:
Hi BeWu
Here is one way
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kvMTTVU0lEKSC0qzs8DsQyQsCEeDFETqwMxwwhuhhEWvQZ4McwMY7gZxjjU4jYPZoYJ3AwTLHoJuCMWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Project = _t, Person = _t, Month1 = _t, Month2 = _t, Month3 = _t, Month4 = _t, Month5 = _t, Month6 = _t, Month7 = _t, Month8 = _t, Month9 = _t, Month10 = _t, Month11 = _t, Month12 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project", type text}, {"Person", type text}, {"Month1", Int64.Type}, {"Month2", Int64.Type}, {"Month3", Int64.Type}, {"Month4", Int64.Type}, {"Month5", Int64.Type}, {"Month6", Int64.Type}, {"Month7", Int64.Type}, {"Month8", Int64.Type}, {"Month9", Int64.Type}, {"Month10", Int64.Type}, {"Month11", Int64.Type}, {"Month12", Int64.Type}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Project", "Person"}, "Attribute", "Value"), #"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"Project", "Person"}, {{"all", each _, type table }}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "new", each [a=Table.AddIndexColumn([all],"Index",0,1), total1 = List.Count(List.Select(a[Value],each _>0)), start=List.PositionOf(a[Value],1), end=List.PositionOf( List.Range(a[Value],start),0)+start, result =Table.AddColumn(a, "custom", each if total1=1 and [Index] = start then "START/END" else if [Index] = start then "START" else if total1>1 and [Index]= end then "END" else null )][result]), #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"new"}), #"Expanded new" = Table.ExpandTableColumn(#"Removed Other Columns", "new", {"Project", "Person", "Attribute", "custom"}, {"Project", "Person", "Attribute", "custom"}), #"Pivoted Column" = Table.Pivot(#"Expanded new", List.Distinct(#"Expanded new"[Attribute]), "Attribute", "custom") in #"Pivoted Column"
1 Reply
Hi BeWu
Here is one way
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kvMTTVU0lEKSC0qzs8DsQyQsCEeDFETqwMxwwhuhhEWvQZ4McwMY7gZxjjU4jYPZoYJ3AwTLHoJuCMWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Project = _t, Person = _t, Month1 = _t, Month2 = _t, Month3 = _t, Month4 = _t, Month5 = _t, Month6 = _t, Month7 = _t, Month8 = _t, Month9 = _t, Month10 = _t, Month11 = _t, Month12 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project", type text}, {"Person", type text}, {"Month1", Int64.Type}, {"Month2", Int64.Type}, {"Month3", Int64.Type}, {"Month4", Int64.Type}, {"Month5", Int64.Type}, {"Month6", Int64.Type}, {"Month7", Int64.Type}, {"Month8", Int64.Type}, {"Month9", Int64.Type}, {"Month10", Int64.Type}, {"Month11", Int64.Type}, {"Month12", Int64.Type}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Project", "Person"}, "Attribute", "Value"), #"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"Project", "Person"}, {{"all", each _, type table }}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "new", each [a=Table.AddIndexColumn([all],"Index",0,1), total1 = List.Count(List.Select(a[Value],each _>0)), start=List.PositionOf(a[Value],1), end=List.PositionOf( List.Range(a[Value],start),0)+start, result =Table.AddColumn(a, "custom", each if total1=1 and [Index] = start then "START/END" else if [Index] = start then "START" else if total1>1 and [Index]= end then "END" else null )][result]), #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"new"}), #"Expanded new" = Table.ExpandTableColumn(#"Removed Other Columns", "new", {"Project", "Person", "Attribute", "custom"}, {"Project", "Person", "Attribute", "custom"}), #"Pivoted Column" = Table.Pivot(#"Expanded new", List.Distinct(#"Expanded new"[Attribute]), "Attribute", "custom") in #"Pivoted Column"