Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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.
Solved! Go to 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
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
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
15 | |
13 | |
13 | |
12 | |
11 |