How to perform a function only for a unique value from a column

[Anonymous]

Hi, i would like to get values ​​only for unique values ​​in the column in Power BI.

This is an example of my current table, in the backlog I used the function: 

'Backlog = IF([REALDATE] <> BLANK(),NETWORKDAYS([REALDATE],[PLANNEDDATE]),BLANK())'

NUMBER	PLANNEDDATE	REALDATE	BACKLOG
1234	04.07.22	11.07.22	-6
1234	04.07.22	11.07.22	-6
0000	04.07.22	14.07.22	-9
9999	04.07.22	08.07.22	-5
9999	04.07.22	08.07.22	-5
9999	04.07.22	08.07.22	-5

and I would like to achieve something like this

NUMBER	PLANNEDDATE	REALDATE	BACKLOG
1234	04.07.22	11.07.22	-6
1234	04.07.22	11.07.22
0000	04.07.22	14.07.22	-9
9999	04.07.22	08.07.22	-5
9999	04.07.22	08.07.22
9999	04.07.22	08.07.22

if the number repeats, I don't want to count the backlog for it. 

How i can do it? 

[karen578 · Accepted solution]

You could add an index column that restarts with each new NUMBER, and then add a new column with your formula conditional on the index.

See Create Row Number for Each Group in Power BI using Power Query - RADACAD.

Steps would include grouping by NUMBER, keeping all rows, adding an index column, then expanding.

Then you could just add your column with the Custom Formula option in the UI.

[Anonymous]

Thank You @karen578 and @serpiva64 for help 🙂

[serpiva64 · Accepted solution]

Hi,

i was working on the same schema as karen578

this is the transformations in power query

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNlHSUTIw0TMw1zMyAjINDeFMXTOlWB3i1BgAAZoaBFPXEqzGEghQ1RhYINSYUk9NLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [NUMBER = _t, PLANNEDDATE = _t, REALDATE = _t, BACKLOG = _t]),
#"Grouped Rows" = Table.Group(Source, {"NUMBER"}, {{"AllRows", each _, type table [NUMBER=nullable text, PLANNEDDATE=nullable text, REALDATE=nullable text, BACKLOG=nullable text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([AllRows],"Index",1,1)),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"NUMBER", "PLANNEDDATE", "REALDATE", "BACKLOG", "Index"}, {"NUMBER", "PLANNEDDATE", "REALDATE", "BACKLOG", "Index"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Custom",{{"NUMBER", type text}, {"PLANNEDDATE", type date}, {"REALDATE", type date}, {"BACKLOG", Int64.Type}, {"Index", Int64.Type}})
in
#"Changed Type"

and finally the dax

Column =

if('Table'[Index]=1, 'Table'[BACKLOG])

If this post is useful to help you to solve your issue, consider giving the post a thumbs up and accepting it as a solution !

[karen578 · Accepted solution]

You could add an index column that restarts with each new NUMBER, and then add a new column with your formula conditional on the index.

See Create Row Number for Each Group in Power BI using Power Query - RADACAD.

Steps would include grouping by NUMBER, keeping all rows, adding an index column, then expanding.

Then you could just add your column with the Custom Formula option in the UI.