Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Conditional indexing in grouped data

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,

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    3 years ago

    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"}),

     

4 Replies

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Community Support
    3 years ago

    Hi, Anonymous ;

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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.

      • v-yalanwu-msft's avatar
        v-yalanwu-msft
        Community Support
        3 years ago

        Hi, Anonymous ;

        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.