Forum Discussion

MURTAZA's avatar
MURTAZA
Resolver I
4 years ago
Solved

Custom Index based on Text Values

Hi All,

I have some reponses and I am trying to see the changes if multiple responses are submitted.
This is the sample data that I have:

 

This is what I want:

 

So each response should be grouped, unless the response changes for the same ID. If i use the Group By function, all the 'YES' will be grouped into 1, whereas, I want Yellow higlighed, Green and Orange, all grouped separately.

Alternatively, if there is a way to produce an Index column like this, I can then use group by and use pivot etc to solve this:

 

Thank you.

Need Help
  • wdx223_Daniel's avatar
    wdx223_Daniel
    4 years ago

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjYyVNJRinQNBpJGBkaGupa6RgZKsTrYZQyRZPz8kSSMcGoxxiljApYxMTDA4QBsMkAHxAIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Response = _t, Date = date]),
    Custom1 = Table.Combine(Table.Group(Source,"ID",{"n",each let a=List.Combine(Table.ToRows(Table.Group(_,"Response",{"n",each List.Max([Date])},0))) in #table({"ID"}&List.TransformMany({1..List.Count(a)/2},each {"Response\-00","Date\-00(Max)"},(x,y)=>Number.ToText(x,y)),{{[ID]{0}}&a})},0)[n])
in
    Custom1

7 Replies

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Community Champion
    4 years ago

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjYyVNJRinQNBpJGBkaGupa6RgZKsTrYZQyRZPz8kSSMcGoxxiljApYxMTDA4QBsMkAHxAIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Response = _t, Date = date]),
    Custom1 = Table.Combine(Table.Group(Source,"ID",{"n",each let a=List.Combine(Table.ToRows(Table.Group(_,"Response",{"n",each List.Max([Date])},0))) in #table({"ID"}&List.TransformMany({1..List.Count(a)/2},each {"Response\-00","Date\-00(Max)"},(x,y)=>Number.ToText(x,y)),{{[ID]{0}}&a})},0)[n])
in
    Custom1
    • MURTAZA's avatar
      MURTAZA
      Resolver I
      4 years ago

      I can't thank you enough. You made it look like a piece of cake. It worked smoothly.

       

      So once I do this transformation, the datatype is not detected automatically. I can detect it for all columns, but I am afraid if new columns are added dynamically, the type will not be detected. Is there a way to add the data type as well in this code?
      For ID and Response, it should be Text
      and for Datetime, it should be DateTime.
      Also the Response column values should be all Uppercase. Thank you 

      • wdx223_Daniel's avatar
        wdx223_Daniel
        Community Champion
        4 years ago

        could insert a step after the step of Source

        ChangeType=Table.TransformColumnTypes(Source,{{"ID", type text},{"Datetime, type datetime},{"Response", type text}})

        to change all response to uppercase, need to do another step

        UpperRespones=Table.TransformColumns(PreivousStepName,{"Response",Text.Upper})