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.
Hi,
based on below table I want to remove duplicate for a new date from table 2, I used calculation table to generate table 2, but some case in the end date show twoic, how we can remove duplicated records.
Solved! Go to Solution.
Hi @Anonymous ,
To create a calculated table as below.
Table = VAR a = CALENDAR ( MAX ( 'Table 1'[start] ), MAX ( 'Table 1'[end] ) ) RETURN ADDCOLUMNS ( a, "end", MAX ( 'Table 1'[end] ), "start", MAX ( 'Table 1'[start] ) )
If you put a SUMMARIZE around your code that generates the table and summarize by your three columns it will return just the unique sets.
TestTable = SUMMARIZE( DATATABLE("start",DATETIME,"end",DATETIME,"new date",DATETIME, { {"1/1/2019","5/1/02019","1/1/2019"}, {"1/1/2019","5/1/02019","2/1/2019"}, {"1/1/2019","5/1/02019","3/1/2019"}, {"1/1/2019","5/1/02019","4/1/2019"}, {"1/1/2019","5/1/02019","5/1/2019"}, {"1/1/2019","5/1/02019","5/1/2019"} }) ,[start],[end],[new date])
thanks for your replay.
kindly note that I want to remove duplicate based on composite kay, mean I want to use three fields as a kay
is your above solution do that?
It will, yes. Summarizing over the three columns will give you one row for each unique set.
Hi @Anonymous ,
To create a calculated table as below.
Table = VAR a = CALENDAR ( MAX ( 'Table 1'[start] ), MAX ( 'Table 1'[end] ) ) RETURN ADDCOLUMNS ( a, "end", MAX ( 'Table 1'[end] ), "start", MAX ( 'Table 1'[start] ) )
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.
User | Count |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |