Power Query Custom/Conditional Grouping

Power Query
1

Hi,

I’m trying to create a custom [Key] which is used to group items. It’s a combination of:

  • [Project]

  • conditional total where if previous row [Concat] is not same as current row [Concat] add +1

PS: simple grouping of [Project] does not work, because you need to take into account Field 1 & Field 2, if it’s filled, the next item should be +1. => see Project B for precise behavior.

Project Field1 Field2 Index Concat Key
A 1 A A1
A 2 A A1
A 3 A A1
B 4 B B1
B 5 B B1
B X 1 6 BX1 B1
B 7 B B2
B Y 1 8 BY1 B2
C 9 C C1
C 10 C C1
D 11 D D1
E 12 E E1
E 13 E E1
E 14 E E1

Many thx in advance

2

Hi @Wilknsn, I noticed you didn’t provide a PBIX file. Providing one will help users and experts find a solution to your inquiry faster and better.

A perfect initial question includes all of the following:

  • A clear explanation of the problem you are experiencing

  • A mockup of the results you want to achieve

  • Your current work-in-progress PBIX file

  • Your underlying data file (to allow us to go into Power Query if necessary to transform your data and/or data model – often DAX questions really end up being data modeling solutions)

Check out this thread on Tools and Techniques for Providing PBIX Files with Your Forum Questions

Not completing your data may sometimes cause delay in getting an answer.

3

Hi @Wlknsn,

Based on this:

I could only achieve the desired result using List.Generate. Copy this into a new blank query.

let
    rowcount = Table.RowCount(ChType), 
    BufferedField1 = List.Buffer( ChType[Field1] ),
    BufferedField2 = List.Buffer( ChType[Field2] ),
    BufferedProject = List.Buffer( ChType[Project] ),
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddBJDoAgEATAv3Dm4IjrkcU/YAj//4Z0QBaRhDn0VBoSnGOScRYPhUGSxDyvYR6BaEFlWMIgqS+sPdj09Ia1pb/W/rbmDHdqHVjfVFDn1hkGSdMHaGrFFMFFiCbKVQSfgHh1IoayVOIf", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Project = _t, Field1 = _t, Field2 = _t, Index = _t, Concat = _t, Key = _t]),
    ReplaceEmpty = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"Field1", "Field2"}),
    ChType = Table.TransformColumnTypes(ReplaceEmpty,{{"Index", Int64.Type}}),
    CreateRecordLists = Table.AddColumn( ChType, "NewColumn", each
        List.Generate( 
            ()=> [Counter=1, row=1, Value=BufferedProject{0}],
            each [row] <= rowcount,
            each 
                if List.IsEmpty( List.RemoveNulls( { BufferedField1{[row]-1}, BufferedField2{[row]-1} } )) and BufferedProject{[row]} <> BufferedProject{[row]-1}
                    then [Counter=1, row=[row]+1, Value= BufferedProject{[row]}] else
                if List.IsEmpty( List.RemoveNulls( { BufferedField1{[row]-1}, BufferedField2{[row]-1} } )) and BufferedProject{[row]} = BufferedProject{[row]-1}
                    then [Counter=[Counter], row=[row]+1, Value= BufferedProject{[row]}] else
                if not List.IsEmpty( List.RemoveNulls( { BufferedField1{[row]-1}, BufferedField2{[row]-1} } )) and BufferedProject{[row]} = BufferedProject{[row]-1}
                    then [Counter=[Counter]+1, row=[row]+1, Value= BufferedProject{[row]}]
                else [Counter=1, row=[row]+1, Value= BufferedProject{[row]+1}]
            ){[Index]-1} // Get record for current row
        ),
    ExpandRecord = Table.ExpandRecordColumn(CreateRecordLists, "NewColumn", {"Counter", "Value"}, {"Counter", "Value"}),
    MergeValues = Table.CombineColumns(Table.TransformColumnTypes(ExpandRecord, {{"Counter", type text}}, "en-US"),{"Value", "Counter"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"CustomKey")
in
    MergeValues

I hope this is helpful.

4 Likes
4

Hi @Wlknsn! Welcome back to the forum :slight_smile:

Glad you are making progress with. your question, did the response provided by @Melissa help you solve your query?

If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

5

Hi Melissa,

I just tried this and it not only works perfectly but it’s also blazing fast.

How does the start actually work? - I mean, you use ChType in advance and it’s only defined later??

1 Like
6

Hi @Wlknsn,

Yes, M code looks sequential but that’s not required, the engine will always follow the dependency chain. I usually place my variables on top so interaction with the UI doesn’t affect them.

Glad to hear this works well for you. :+1:

1 Like
7

Thanks again, just FYI, I added an additional line in the function to cover the case where you would have just one unique line after the other (in the sample, there would always be at least 2 per project).
=> 3rd if statement

each 
            if List.IsEmpty( List.RemoveNulls( { BufferedField1{[row]-1} } )) and BufferedProject{[row]} <> BufferedProject{[row]-1}
                then [Counter=1, row=[row]+1, Value= BufferedProject{[row]}] else
            if List.IsEmpty( List.RemoveNulls( { BufferedField1{[row]-1} } )) and BufferedProject{[row]} = BufferedProject{[row]-1}
                then [Counter=[Counter], row=[row]+1, Value= BufferedProject{[row]}] else
            if not List.IsEmpty( List.RemoveNulls( { BufferedField1{[row]-1} } )) and BufferedProject{[row]} <> BufferedProject{[row]-1}
                then [Counter=1, row=[row]+1, Value= BufferedProject{[row]}] else
            if not List.IsEmpty( List.RemoveNulls( { BufferedField1{[row]-1} } )) and BufferedProject{[row]} = BufferedProject{[row]-1}
                then [Counter=[Counter]+1, row=[row]+1, Value= BufferedProject{[row]}]
            else [Counter=1, row=[row]+1, Value= BufferedProject{[row]+1}]
        ){[Index]-1} // Get record for current row
    ),

Custom Grouping | Conditional Grouping | Dynamic segmentation | Dynamic grouping

1 Like