Forum Discussion
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...
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
could you post your code or a snapshoot?
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.
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.