Max Frequency Consecutive Count in Power Query

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,