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

  • MURTAZA's avatar
    MURTAZA
    Resolver I
    4 years ago

    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's avatar
      wdx223_Daniel
      Community Champion
      4 years ago

      could you post your code or a snapshoot?

      • MURTAZA's avatar
        MURTAZA
        Resolver I
        4 years ago
        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.