Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Calculation from Spotfire to Power BI

Hi All, I have calculation in the spotfire mentioning below.I have two columns Start date and End Date and need to filter based on the condition. Transformation - Filtered Rows [Start Date]>= [End...
Need Help
  • V-lianl-msft's avatar
    V-lianl-msft
    4 years ago

    Hi Anonymous ,

     

    1. Add a custom column:

    2. Filter on TRUE.

    3. Remove the custom column.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TYxLDoAwCAXvwrokBUXrWUgX9Xf/IwgaIrvJm8lThQEFuDIhoQQyNuhFYY9hwjVwsczdEYPgFjhb5u78BrZPkmC2zuX1D1RT+Mo7yZYkQ+8P", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Task = _t, Start = _t, End = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Task", type text}, {"Start", type date}, {"End", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [Start]>=[End]),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = true)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom"})
in
    #"Removed Columns"


    Best Regards,
    Liang
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

V-lianl-msft's avatar
V-lianl-msft
Community Support
4 years ago

Hi Anonymous ,

 

1. Add a custom column:

2. Filter on TRUE.

3. Remove the custom column.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TYxLDoAwCAXvwrokBUXrWUgX9Xf/IwgaIrvJm8lThQEFuDIhoQQyNuhFYY9hwjVwsczdEYPgFjhb5u78BrZPkmC2zuX1D1RT+Mo7yZYkQ+8P", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Task = _t, Start = _t, End = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Task", type text}, {"Start", type date}, {"End", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [Start]>=[End]),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = true)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom"})
in
    #"Removed Columns"


Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.