Forum Discussion
Power Bi, Dax query
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
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".
9 Replies
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".
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.
Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.
Hii,
By using the above expression, the data refresh time has been increased & It taking a lot of time.
Any solution for that..?
Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.
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"Read about Table.Buffer and use it in an intermediate step.
