Forum Discussion
Resolver ICustom Index based on Text Values
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
Community Champion
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
Resolver II 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?