Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
Anonymous
Not applicable

Consecutive values on multiple columns

Hello,

 

I hope you can help me with a case I have spent hours on.

I am struggling with consecutive values where they are spread over several columns.

ozod_0-1695402740835.png

You can access the sample data via this link.

 

As you can see, the first column is set to dates. The other columns show personnel with daily values: 0, 1, and 2 in each cell.

The job here is to create a query to find 14 or more successive values of "2" without any interruption on all columns other than "Date". 

 

The final output should give the start date of those values if they exist. 

ozod_1-1695403690266.png

 

I have looked up and found out that columns can be grouped with "GroupLocal" for consecutive values.

However, I couldn't apply this technique on dynamic columns at once (the number of columns and names are changing).

 

Is it possible to achieve the desired result in the end?

 

Let me know if you have questions.

Thank you in advance! 

1 ACCEPTED SOLUTION
slorin
Super User
Super User

Hi,

another solution

 

let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
DatesList = List.Buffer(Source[Date]),
Table = #table(
List.Skip(Table.ColumnNames(Source)),
{List.Transform(
List.Skip(Table.ToColumns(Source)),
each try DatesList{Text.PositionOf(
Text.Combine(List.Transform(_,Text.From)),
Text.Repeat("2",12))}
otherwise "--")}
)
in
Table

Stéphane 

View solution in original post

5 REPLIES 5
slorin
Super User
Super User

Hi,

another solution

 

let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
DatesList = List.Buffer(Source[Date]),
Table = #table(
List.Skip(Table.ColumnNames(Source)),
{List.Transform(
List.Skip(Table.ToColumns(Source)),
each try DatesList{Text.PositionOf(
Text.Combine(List.Transform(_,Text.From)),
Text.Repeat("2",12))}
otherwise "--")}
)
in
Table

Stéphane 

Anonymous
Not applicable

thanks @slorin !

I have made minor adjustments and it works as intended in just 2 steps.

ImkeF
Community Champion
Community Champion

Hi @Anonymous ,
you can use this method then. It is using GroupKind.Local as you suggested, but for each column separately:

let
    Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
    ColumnNames = List.Buffer(List.Skip(Table.ColumnNames(Source))), 
    ListOfColumns = Table.ToColumns(Source), 
    DatesList = List.Buffer(ListOfColumns{0}), 
    ListOfPersonColumns = List.Skip(ListOfColumns), 
    Custom1 = Table.FromColumns({ListOfPersonColumns, ColumnNames}, {"Values", "Person"}), 
    AddTable = Table.AddColumn(
        Custom1, 
        "Custom", 
        each Table.FromColumns({DatesList, [Values]}, {"Date", "Value"})
    ), 
    AddGrouping = Table.AddColumn(
        AddTable, 
        "Grouped", 
        each Table.Group(
            [Custom], 
            {"Value"}, 
            {{"StartDate", each List.Min(_[Date])}, {"Count", List.Count}}, 
            GroupKind.Local
        )
    ), 
    #"Added Custom" = Table.AddColumn(
        AddGrouping, 
        "Custom.1", 
        each Table.SelectRows([Grouped], (inner) => inner[Count] >= 14)
    ), 
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom", {"Person", "Custom.1"}), 
    #"Expanded Custom.1" = Table.ExpandTableColumn(
        #"Removed Other Columns", 
        "Custom.1", 
        {"StartDate"}, 
        {"StartDate"}
    ), 
    #"Transposed Table" = Table.Transpose(#"Expanded Custom.1"), 
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars = true])
in
    #"Promoted Headers"

Also, see file attached.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

ImkeF
Community Champion
Community Champion

Hi @Anonymous ,
do you really just want to see the first date where there are more than 14 consequtive values?

See person 5 for example, there is another sequence starting at the 11th of march.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Anonymous
Not applicable

Hi @ImkeF 

 

I intentionally marked with a big "X" the 5th person to remind myself of this double sequence. 

But, yeah, just the first date that matches the criteria. The other set in the column can be omitted in the search.

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.