Max Frequency Consecutive Count in Power Query

[Darko_Giac]

Hi all,

I'm having a slight issue with modifying a formula in power query to count the maximum number of times a number appears consecutively.

When I import the data using "From Table", the formula seems to work just fine, however, I am pointing power query to a folder, and it seems to be causing an issue. 

Essentially, I am working with survey data and am tracking response frequencies.  I would like to count the maximum number of times a "0", "1", "2", "3" appears in a row as well as how many times a respondent chose "99".

I have the following code: 

let
    Source = Folder.Files("C:\Users\darko.giacomini\Desktop\TestQueryWorkbook\RawDataFiles(SourceFolder)"),
    ValuesToCount = {0..3, 99},
    AddedColumns = 
        List.Accumulate(
            List.Positions(ValuesToCount), 
            Source, 
            (accum, curr) =>
            if ValuesToCount{curr} = 99 then
                Table.AddColumn(
                    accum, 
                    "Skipped", 
                    each List.Count(List.Select(List.Skip(Record.FieldValues(_), 4), (i) => i = ValuesToCount{curr}))
                )
            else
                Table.AddColumn(
                    accum,
                    "Max" & Text.From(ValuesToCount{curr}) & "inarow", 
                    each 
                        let
                           Positions = List.PositionOf(List.Skip(Record.FieldValues(_), 4), ValuesToCount{curr}, Occurrence.All),
                           Transformed = List.Transform(List.Positions(Positions), (curr1) => Positions{curr1} - Positions{0} - curr1),
                           DistinctValues = List.Distinct(Transformed),
                           MaxCount = List.Max(List.Transform(DistinctValues, (curr2) => List.Count(List.Select(Transformed, each _ = curr2))))
                        in
                           MaxCount                 
               )
       )
in
    AddedColumns

However, this only creates another table that I cannot expand the new variables into ("Max0inarow" - "Skipped"). Again, I think it's because I'm using a folder to pull in the data rather than from a table within the workbook. 

This is the result I am getting pulling from a folder:

When I click the double drop-down arrows in the "Content" column to expand the table, it removes the columns I had inserted using the code, so what I end up with is this screen:

And this is the result I am looking for:

Also attached is a sample workbook I am currently using:

SampleWorkbook

[v-jiascu-msft · Accepted solution]

Hi @Darko_Giac,

Please download the demo from the attachment. I'm afraid you put the code in the wrong order. Firstly, we need to import data to Power BI or Excel with either Folder or File. Then we can add more customized columns. It could be like below.

let
    Source = Folder.Files("D:\Downloads\TestQueryWorkbook\RawDataFiles(SourceFolder)"),
    ValuesToCount = {0..3, 99},
    
    #"Expanded Attributes" = Table.ExpandRecordColumn(Source, "Attributes", {"Content Type", "Kind", "Size", "ReadOnly", "Hidden", "System", "Directory", "Archive", "Device", "Normal", "Temporary", "SparseFile", "ReparsePoint", "Compressed", "Offline", "NotContentIndexed", "Encrypted"}, {"Attributes.Content Type", "Attributes.Kind", "Attributes.Size", "Attributes.ReadOnly", "Attributes.Hidden", "Attributes.System", "Attributes.Directory", "Attributes.Archive", "Attributes.Device", "Attributes.Normal", "Attributes.Temporary", "Attributes.SparseFile", "Attributes.ReparsePoint", "Attributes.Compressed", "Attributes.Offline", "Attributes.NotContentIndexed", "Attributes.Encrypted"}),
    #"Filtered Hidden Files1" = Table.SelectRows(#"Expanded Attributes", each [Attributes]?[Hidden]? <> true),
    #"Filtered Hidden Files2" = Table.SelectRows(#"Filtered Hidden Files1", each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files2", "Transform File from Query1", each #"Transform File from Query1"([Content])),
    #"Removed Other Columns1" = Table.SelectColumns(#"Invoke Custom Function1", {"Transform File from Query1"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from Query1", Table.ColumnNames(#"Transform File from Query1"(#"Sample File"))),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Response ID", Int64.Type}, {"Country", type text}, {"Gender", Int64.Type}, {"Race", Int64.Type}, {"PFi1", Int64.Type}, {"PFi2", Int64.Type}, {"PFi3", Int64.Type}, {"PFi4", Int64.Type}, {"PFi5", Int64.Type}, {"PFi6", Int64.Type}, {"PFi7", Int64.Type}, {"PFi8", Int64.Type}, {"PFi9", Int64.Type}, {"PFi10", Int64.Type}}),
    AddedColumns = 
        List.Accumulate(
            List.Positions(ValuesToCount), 
            #"Changed Type", 
            (accum, curr) =>
            if ValuesToCount{curr} = 99 then
                Table.AddColumn(
                    accum, 
                    "Skipped", 
                    each List.Count(List.Select(List.Skip(Record.FieldValues(_), 4), (i) => i = ValuesToCount{curr}))
                )
            else
                Table.AddColumn(
                    accum,
                    "Max" & Text.From(ValuesToCount{curr}) & "inarow", 
                    each 
                        let
                           Positions = List.PositionOf(List.Skip(Record.FieldValues(_), 4), ValuesToCount{curr}, Occurrence.All),
                           Transformed = List.Transform(List.Positions(Positions), (curr1) => Positions{curr1} - Positions{0} - curr1),
                           DistinctValues = List.Distinct(Transformed),
                           MaxCount = List.Max(List.Transform(DistinctValues, (curr2) => List.Count(List.Select(Transformed, each _ = curr2))))
                        in
                           MaxCount                 
               )
       )
in
    AddedColumns

Best Regards,

[Darko_Giac]

Thanks @v-jiascu-msft! Worked like a charm!