Power Bi, Dax query

[Vishnu812]

 

case 1: Here compare the rows of id if it is same then check its corresponding values in task column (consider only A and B ignore any other values). For example if id 1 has both A and B in task then insert name of Value against B in output column and value against A make empty and ignore C value (Keep it same) as shown in above example.

case 2: If a id has only 'A' in task then keep the name has it is (fo rexample refer id 3 from above table)

Overal summary : if a id has both A and B in taks then keep B value same and make A value empty. And if id has only A then keep the name value same in output column and ignore the other task values (like C and other) and keep its name value same

[lbendlin · Accepted solution]

No need for DAX.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIE4sSkZKVYHQjfCYgrKqvgfGcgzs3LB/ONoOoLCovgfCck/cZQ+eTkRDDfBGZ+cQqcD1KfllIM5ptC5QvLU+F8kH1p6RlKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, Task = _t, Name = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Output", each if [Task] = "A" then if Table.RowCount(Table.SelectRows(Source,(k)=> k[Id]=[Id] and k[Task]="B"))=0 then [Name] else null
else [Name])
in
    #"Added Custom"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".

[Vishnu812]

Hi @Ibendlin,

The below code is working correclty as needed but it taking lot of time to refresh like the refresh count goes 3 times of the original data. Any solution for that. Without adding that custom column it will refresh in seconds but after adding this it taking arround 10 minutes of time.

#"Added Custom" = Table.AddColumn(#"Reordered Columns", "Custom", each if [task] = "A" then if Table.RowCount(Table.SelectRows(#"Reordered Columns",(k)=> k[id]=[id] and k[task]="B"))=0 then [name] else null
else [name])
in
#"Added Custom"

[lbendlin]

Read about Table.Buffer and use it in an intermediate step.

[Vishnu812]

Hii,

By using the above expression, the data refresh time has been increased & It taking a lot of time.

Any solution for that..? 

[lbendlin]

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[Vishnu812]

Hi @Ibendlin thanks for your response.

The above provided data is just a sample data and ur solution is working for this data only can you provide common solution or explain above code.

Thank you.

[lbendlin]

Your reference is wrong.  It needs to refer the previous step.

Change from 

Table.SelectRows(Source,

to 

Table.SelectRows(#"Changed Type",

[lbendlin]

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[Vishnu812]

Hello, 

As u can see in above snap, the above solution working when I create new query. But I want to add it to the existing table, by doing that it showing error as above pls correct me if any mistake in above query.