Forum Discussion
Helper IIINeed to create new Rows and Replicate Results
I have an odd problem, data set that I am working with, has only partial information.
Meaning, "Action" column, has "See Previous Action(s)" as a input however, it should have "Actions" repeat from "Issue" column
Top chart is the raw data, bottom is the desired result
If case # and employee # have "See Previous Action(s)" as input then
New row should be created for each instance of Issue that is filled in
If case # and employee # do not have "See Previous Action(s)" as input, then nothing needs to happen
This is not even DAX at this point, can someone help me with this?
What is your definition of "Previous action"? I don't see an index column or a timestamp. How many previous actions?
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRsrQwB5IuiUXZCkH5+blAdnBqqkJAUWpZZn5psYJjcklmfp5GsaZSrE60krGRIVyLX76CT2Z6RgmQ6ZyYk6PgmpOaXFKUmZyZmIdXbUZqcjaEl1Sak6QUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Case #" = _t, #"Employee #" = _t, Issue = _t, Action = _t]), #"Replaced Value" = Table.ReplaceValue(Source,each [Action],each if [Action] = "See Previous Action(s)" then Table.SelectRows(Source, (k)=> [#"Employee #"]=k[#"Employee #"] and k[Action]<>"See Previous Action(s)")[Action] else {[Action]},Replacer.ReplaceValue,{"Action"}), #"Expanded Action" = Table.ExpandListColumn(#"Replaced Value", "Action") in #"Expanded Action"How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".
2 Replies
- lbendlin
Super User
What is your definition of "Previous action"? I don't see an index column or a timestamp. How many previous actions?
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRsrQwB5IuiUXZCkH5+blAdnBqqkJAUWpZZn5psYJjcklmfp5GsaZSrE60krGRIVyLX76CT2Z6RgmQ6ZyYk6PgmpOaXFKUmZyZmIdXbUZqcjaEl1Sak6QUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Case #" = _t, #"Employee #" = _t, Issue = _t, Action = _t]), #"Replaced Value" = Table.ReplaceValue(Source,each [Action],each if [Action] = "See Previous Action(s)" then Table.SelectRows(Source, (k)=> [#"Employee #"]=k[#"Employee #"] and k[Action]<>"See Previous Action(s)")[Action] else {[Action]},Replacer.ReplaceValue,{"Action"}), #"Expanded Action" = Table.ExpandListColumn(#"Replaced Value", "Action") in #"Expanded Action"How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".
StoryofDataThank you, this works with some slight modifications 🙂