Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
MURTAZA
Resolver I
Resolver I

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:

MURTAZA_0-1634092799700.png

 

This is what I want:

MURTAZA_1-1634092834745.png

 

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:

MURTAZA_2-1634093009229.png

 

Thank you.

1 ACCEPTED SOLUTION
wdx223_Daniel
Super User
Super User

wdx223_Daniel_0-1634101942413.png

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

View solution in original post

7 REPLIES 7
wdx223_Daniel
Super User
Super User

wdx223_Daniel_0-1634101942413.png

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

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 

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

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. 

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

 

MURTAZA_0-1634185667051.png


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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors
Top Kudoed Authors