Custom Index based on Text Values

[MURTAZA]

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.

[wdx223_Daniel · Accepted solution]

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]

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]

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

[MURTAZA]

Thanks @wdx223_Daniel for your response. The data is already formatted after the source. When I add the custom function that you posted in your first query, all the columns become unidentified type. 

[wdx223_Daniel]

could you post your code or a snapshoot?

[MURTAZA]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dc6xCoAwEAPQXymdLSSppdhNwVEdnIr4/7+hqEOFdrnl3eVyHNaLtrN53u8piG5wgj27urCQdStAzRPflP6RHmgUqMlbIOBfAIMDDWNCLHwZ8zSXK9EgQa0EekOmr1U9QTRSCveT8wI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Response = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Response", type text}, {"Date", type datetime}}),
    Custom1 = Table.Combine(Table.Group(#"Changed Type","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

Here is a link to download the file:
https://drive.google.com/file/d/1v0WqeguV0MHxOZSh9WbK3jgBQ0u6jt3S/view?usp=sharing 

I am hoping that the column type could change automatically, with additional of new dynamic columns. Thank you.

[wdx223_Daniel]

just got what you may need, please add this new step

Custom2= Table.TransformColumnTypes(Custom1,List.Transform(Table.ColumnNames(Custom1),each {_,if Text.StartsWith(_,"Date") then type datetime else type text}))

but, this may slow your code.