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,
how can I smooth string values in a column?
I have time series data (timestamp; string) with some errors or gaps in it:
timestamp;string
I would like to fill the gap and replace the error ("x") with the values in proximity (lets say we want the most frequent value looking at the last 2 and next 2 values). You could call this moving average with strings. The result in this simple example would be all "a" in the string-column.
I feel like this comes close, but MAXA does not work with strings of course:
Smooth = CALCULATE ( CALCULATE ( MAXA( 'timeseries'[string] ); 'timeseries'[Datetime] >= VALUES ( 'timeseries'[Datetime] ) - 4 ; 'timeseries'[Datetime] <= VALUES ( 'timeseries'[Datetime] ) ); ALLEXCEPT ( 'timeseries'; 'timeseries'[Tag];'timeseries'[Logfile];'timeseries'[Datetime] ) )
Any Ideas would be greatly appreciated. I was not able to find a solution.
Hi @strachi,
Can you share a complete sample please? I can't convert the "timestamp" into a time or a date.
Best Regards,
Dale
Hi @v-jiascu-msft, thanks for your reply.
In fact we can further simplify. The "timestamp" does not matter here. The first column is just to indicate the order of the timeseries data. Your can think of it as an ordered index.
Source:
timestamp;string
Result I am looking for:
timestamp;string
The "blank" and the "x" are errors to be identified by looking at the previous and following values in the series. They should be replaced by the most frequent value "in the neighbourhood".
Thank you for giving it another thought.
I am trying to use this to narrow down the strings in proximity to the data gap...
FILTER(Table1;Table1[Index]<=EARLIER(Table1[Index])+1 && Table1[Index]>=EARLIER(Table1[Index])-1)
I guess this could help me I do not succeed in putting it together in a calculated column:
Most Frequent String = FIRSTNONBLANK ( TOPN ( 1; VALUES ( Table1[string] ); RANKX( ALL( Table1[string] ); COUNTROWS(Table1);;ASC) ); 1 )
Anyone?
Hi,
how can I smooth string values in a column?
I have time series data (timestamp; string) with some errors or gaps in it:
timestamp;string
I would like to fill the gap and replace the error ("x") with the values in proximity (lets say we want the most frequent value looking at the last 2 and next 2 values). You could call this moving average with strings. The result in this simple example would be all "a" in the string-column.
I feel like this comes close, but MAXA does not work with strings of course:
Smooth = CALCULATE ( CALCULATE ( MAXA( 'timeseries'[string] ); 'timeseries'[Datetime] >= VALUES ( 'timeseries'[Datetime] ) - 4 ; 'timeseries'[Datetime] <= VALUES ( 'timeseries'[Datetime] ) ); ALLEXCEPT ( 'timeseries'; 'timeseries'[Tag];'timeseries'[Logfile];'timeseries'[Datetime] ) )
Any Ideas would be greatly appreciated. I was not able to find a solution.
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 | |
97 | |
78 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |