Forum Discussion

JP-Ronse's avatar
JP-Ronse
Helper II
8 years ago
Solved

year to date

Hi,   I have a huge table with about 90,000 rows, one column contains the date starting somewhere in 2015. Each date is repeated several times which explains the number of rows.   If I put a filt...
Need Help
JP-Ronse's avatar
JP-Ronse
Helper II
8 years ago

Hi All,

 

Thanks for all the feedback. I have to go back to school and that at my age (62) :smileyfrustrated:

There are a lot of interesting links in the replies I have to go through.

 

It is no really YTD but for now I keep it as:

 

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DAY", type datetime}, {"THRESHOLD", Int64.Type}, {"MIN_OCCURENCES", Int64.Type}, {"METRIC", type text}, {"RESOURCE_NAME", type text}, {"CONT_BREACHING_COUNT", Int64.Type}, {"SEPARATED_BREACHING_COUNT", Int64.Type}, {"islast<I1>months", type any}, {"12", type any}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each Date.IsInYearToDate([DAY]) or Date.IsInPreviousNMonths([DAY], 12)),
    #"Split Column by Delimiter" = Table.SplitColumn(Table.TransformColumnTypes(#"Filtered Rows", {{"DAY", type text}}, "en-GB"),"DAY",Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv),{"DAY.1", "DAY.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"DAY.1", type date}, {"DAY.2", type time}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"DAY.2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"DAY.1", "DAY"}}),
    #"Filtered Rows1" = Table.SelectRows(#"Renamed Columns", each ([METRIC] = "UTILIN")),
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows1",{"islast<I1>months", "12"})
in
    #"Removed Columns1"

Thanks all,

 

JP-Ronse