Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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".
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,
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.
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 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.
Your reference is wrong. It needs to refer the previous step.
Change from
Table.SelectRows(Source,
to
Table.SelectRows(#"Changed Type",
Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.