Max Frequency Consecutive Count in Power Query

Question

&nbsp;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.&nbsp;&nbsp;Essentially, I am working with survey data and am tracking response frequencies.&nbsp; 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:&nbsp;&nbsp;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) =&gt;
            if ValuesToCount{curr} = 99 then
                Table.AddColumn(
                    accum, 
                    "Skipped", 
                    each List.Count(List.Select(List.Skip(Record.FieldValues(_), 4), (i) =&gt; i = ValuesToCount{curr}))
                )
            else
                Table.AddColumn(
                    accum,
                    "Max" &amp; Text.From(ValuesToCount{curr}) &amp; "inarow", 
                    each 
                        let
                           Positions = List.PositionOf(List.Skip(Record.FieldValues(_), 4), ValuesToCount{curr}, Occurrence.All),
                           Transformed = List.Transform(List.Positions(Positions), (curr1) =&gt; Positions{curr1} - Positions{0} - curr1),
                           DistinctValues = List.Distinct(Transformed),
                           MaxCount = List.Max(List.Transform(DistinctValues, (curr2) =&gt; List.Count(List.Select(Transformed, each _ = curr2))))
                        in
                           MaxCount                 
               )
       )
in
    AddedColumnsHowever, 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.&nbsp;&nbsp;This is the result I am getting pulling from a folder:&nbsp;When I click the double drop-down arrows in the "Content" column to expand the table,&nbsp;it removes the columns I had inserted using the code, so what I end up with is this screen:&nbsp;&nbsp;And this is the result I am looking for:&nbsp;&nbsp;Also attached is a sample workbook I am currently using:&nbsp;SampleWorkbook&nbsp;&nbsp;

v-jiascu-msft · Accepted Answer

Hi&nbsp;Darko_Giac,
&nbsp;
Please download the demo from the attachment. I'm afraid you put the code in the&nbsp;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.
&nbsp;
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]? &lt;&gt; true),
    #"Filtered Hidden Files2" = Table.SelectRows(#"Filtered Hidden Files1", each [Attributes]?[Hidden]? &lt;&gt; 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) =&gt;
            if ValuesToCount{curr} = 99 then
                Table.AddColumn(
                    accum, 
                    "Skipped", 
                    each List.Count(List.Select(List.Skip(Record.FieldValues(_), 4), (i) =&gt; i = ValuesToCount{curr}))
                )
            else
                Table.AddColumn(
                    accum,
                    "Max" &amp; Text.From(ValuesToCount{curr}) &amp; "inarow", 
                    each 
                        let
                           Positions = List.PositionOf(List.Skip(Record.FieldValues(_), 4), ValuesToCount{curr}, Occurrence.All),
                           Transformed = List.Transform(List.Positions(Positions), (curr1) =&gt; Positions{curr1} - Positions{0} - curr1),
                           DistinctValues = List.Distinct(Transformed),
                           MaxCount = List.Max(List.Transform(DistinctValues, (curr2) =&gt; List.Count(List.Select(Transformed, each _ = curr2))))
                        in
                           MaxCount                 
               )
       )
in
    AddedColumns
&nbsp;
&nbsp;
Best Regards,

Forum Discussion

Max Frequency Consecutive Count in Power Query

2 Replies

Related content

DAX Count consecutive

Counting consecutive dates

Frequency Count

Counting consecutive days (Dax)

DAX - Frequency Count.