How to count sequence per group?

[Anonymous]

I have this data table

I would like to count all sequences (STATUS) by group (MAT.), without grouping the sequences.

I created a script in R to do this count and it works perfectly, however when the script is used in the power query, an error is occurring with the data (Wrong Dates).

Why is this error occurring? Is there any way to do this count in Power Query or through DAX?

My dataset

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fdcxTgMxEEbhu2yNFM+M7bFLhAQdNNtF3P8abJCl2I6fS/iKwNv80s79fqRc7Xg7VG6h3jSoXD98HL9vT1EU6+QcJHby+TNQYspMzlSYKpIFpCA3CUDKZEyRKTFlJmcqHQ3P5Pp3nzI8x+vXJIKiKIYSURJKRnGUgoINFBsoNlBsoNhAsYFiA830TJW/CFqYKpIFps0sLhJeDJExRabElJmcqTBVpMdmiPoa5zwawdGARJS+xPs5r4bI8a8r+EmVRAOKoGAFxQqKFTShZBRHwQaKDSxQ0cculHdBZEyRKTFlJmcqTBXpsQsiriFcQ7iGcA3hGsI1hGsI15C+xjmvQ3EdIIpiKBEloWQURykolcSwgWGDazfhf1H6IopiKBEloWQU7+ScBvOUl8EQXYNBEiZlMqbIlJgykzNxDeEauqrhHo/pQplImYwpvp4vTfob5f37a7C8Md9Y2VhlGw6VyYZLZTbdmG0sbixtLG/MN1Y2VtmGo2W2TZfhcBmeuqwuuEarC67R6oJrtLrgGq1e3ButXtwbrV7c21dofnH//QM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"MAT." = _t, DATA = _t, STATUS = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"MAT.", Int64.Type},{"STATUS", type text}}),
    #"Run R script" = R.Execute("library(dplyr)#(lf)library(tidyr)#(lf)#(lf)df = dataset %>% #(lf)  group_by(run = data.table::rleid(MAT., STATUS), MAT.) %>% #(lf)  summarise(count = paste(STATUS[1], n(), min(DATA), max(DATA), sep = ' '))#(lf)#(lf)#(lf)df2 = df %>%#(lf)  separate(count, c(""Status"", ""Count"", ""Inicio"", ""Fim""), ' ')",[dataset=#"Changed Type"]),
    df2 = #"Run R script"{[Name="df2"]}[Value]
in
    df2

[AlexisOlson · Accepted solution]

Here's a purely M solution:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fdcxTgMxEEbhu2yNFM+M7bFLhAQdNNtF3P8abJCl2I6fS/iKwNv80s79fqRc7Xg7VG6h3jSoXD98HL9vT1EU6+QcJHby+TNQYspMzlSYKpIFpCA3CUDKZEyRKTFlJmcqHQ3P5Pp3nzI8x+vXJIKiKIYSURJKRnGUgoINFBsoNlBsoNhAsYFiA830TJW/CFqYKpIFps0sLhJeDJExRabElJmcqTBVpMdmiPoa5zwawdGARJS+xPs5r4bI8a8r+EmVRAOKoGAFxQqKFTShZBRHwQaKDSxQ0cculHdBZEyRKTFlJmcqTBXpsQsiriFcQ7iGcA3hGsI1hGsI15C+xjmvQ3EdIIpiKBEloWQURykolcSwgWGDazfhf1H6IopiKBEloWQU7+ScBvOUl8EQXYNBEiZlMqbIlJgykzNxDeEauqrhHo/pQplImYwpvp4vTfob5f37a7C8Md9Y2VhlGw6VyYZLZTbdmG0sbixtLG/MN1Y2VtmGo2W2TZfhcBmeuqwuuEarC67R6oJrtLrgGq1e3ButXtwbrV7c21dofnH//QM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"MAT." = _t, DATA = _t, STATUS = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"MAT.", Int64.Type}, {"STATUS", type text}, {"DATA", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Date", each
        #date(
            Number.FromText(Text.End([DATA],4)),
            Number.FromText(Text.BetweenDelimiters([DATA],"/","/")),
            Number.FromText(Text.BeforeDelimiter([DATA],"/"))
        ), type date),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"DATA"}),
    #"Sorted Rows" = Table.Sort(#"Removed Columns",{{"MAT.", Order.Ascending}, {"Date", Order.Ascending}}),
    T = Table.Buffer(#"Sorted Rows"),
    #"Generate Records" = Table.FromRecords(
        List.Generate(
            () => [i = 1, n = 1, prior = [M = 0, S =""], curr = [M = T[#"MAT."]{0}, S = T[STATUS]{0}]],
            each [i] < Table.RowCount(T),
            each [
                n = if prior <> curr then [n] + 1 else [n],
                prior = [curr],
                curr = [M = T[#"MAT."]{i}, S = T[STATUS]{i}],
                i = [i] + 1
            ],
            each T{[i]} & [run = [n]]
        ), type table [#"MAT." = Int64.Type, STATUS = Text.Type, Date = Date.Type, run = Int64.Type]),
    #"Grouped Rows" = Table.Group(#"Generate Records", {"MAT.", "STATUS", "run"}, {{"Length", each Table.RowCount(_), Int64.Type}, {"StartDate", each List.Min([Date]), type date}, {"EndDate", each List.Max([Date]), type date}})
in
    #"Grouped Rows"

[AlexisOlson]

The error is because your date column is text type and it's ordering alphabetically rather than understanding as a date.

This is possible with M, though not super straightforward since you can have multiple runs per MAT. and Status.

[Anonymous]

Hi @AlexisOlson ,
The problem is that if this column is changed with a date column before applying the R Script error occurs in the output of the data columns (Microsoft.OleDb.Date).

[AlexisOlson]

I'm not sure if you mean you're getting an error trying to convert to date or the R script can't handle dates for some reason.