Conditional indexing in grouped data

[nicplancho]

Hi,

I have a set of original data that I have already grouped by, and this is working just fine.

[DELETED]

I apply this code to this data :

#"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows1",{{"Source.Name", type text}, {"Team", type text}, {"Project category", type text}, {"Project type", type text}, {"Role", type text}, {"Month", type text}, {"Value", type number

    #"Grouped Rows" = Table.Group(#"Changed Type", {"Team", "Project category", "Month"}, {{"Count", each _, type table [Source.Name=nullable text, Team=nullable text, Project category=nullable text, Project type=nullable text, Role=nullable text, Month=nullable text, Value=nullable number, Index=number]}}),

    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count], "#FCST", 1, 1))

I get the following output :

[DELETED]

This output is fine, but I would like the #FCST column to iterate only when the value of Source.Name changes. Would you have any clue about how I could do this please ?

Thanks in advance,

[v-yalanwu-msft]

Hi, @nicplancho ;

I create a simple example. then you could add a funtion.

= Table.Group(#"Changed Type", {"Cate"}, {{"Index", each Table.AddIndexColumn(_ ,  "Index", 0, 1, Int64.Type)}})

Then expend it.

Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTK1UIrVgTBNLOFMiKATkGVkCGciWEZwljGcZQlnWSCLxQIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Cate = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Cate", type text}, {"Value", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Cate"}, {{"Index", each Table.AddIndexColumn(_ ,  "Index", 0, 1, Int64.Type)}}),
    #"Expanded Index" = Table.ExpandTableColumn(#"Grouped Rows", "Index", {"Index"}, {"Index.Index"})
in
    #"Expanded Index"

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[nicplancho]

Hi Yalan Wu,

Here is what I have played :

#"Grouped Rows" = Table.Group(#"Changed Type", {"Team", "Project category", "Month"}, {{"Index", each Table.AddIndexColumn(_ ,  "Index", 0, 1, Int64.Type)}}),

    #"Expanded Index" = Table.ExpandTableColumn(#"Grouped Rows", "Index", {"Project type", "Role", "Value","Index"}, {"Project type", "Role", "Value", "Index.Index"})

Here is the result :

Given that I have only 2 source files, the index should not go beyond two values, but it goes up to 30 values here unfortunately.

[nicplancho · Accepted solution]

Hi,

I finally solved my issue with this trick :

#"Grouped Rows" = Table.Group(#"Changed Type", {"Team", "Project category", "Month"}, {{"Count", each _, type table [Source.Name=nullable text, Team=nullable text, Project category=nullable text, Project type=nullable text, Role=nullable text, Month=nullable text, Value=nullable number, Index=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddRankColumn([Count], "#FCST", {"Source.Name", Order.Ascending}, [RankKind = RankKind.Dense])),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Project type", "Role", "Value", "#FCST"}, {"Project type", "Role", "Value", "#FCST"}),

[v-yalanwu-msft]

Hi, @nicplancho ;

May be:

#"Grouped Rows" = Table.Group(#"Changed Type", {"Team", "Project category","Project type", "Month"}, {{"Index", each Table.AddIndexColumn(_ ,  "Index", 0, 1, Int64.Type)}}),

    #"Expanded Index" = Table.ExpandTableColumn(#"Grouped Rows", "Index", { "Role", "Value","Index"}, {"Project type", "Role", "Value", "Index.Index"})

Or can you share the result what you want to output to let me understand you logic?

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.