Forum Discussion
Helper IExpanding a data column with mixed Text and Records
I'm attempting to expand a Record column however, there is text "Any" on some rows. Observing: Expression.Error: We cannot convert the value "Any" to type Record. (screenshots)
Question, is there a way to expand the rows that have Records and skip past the text "Any"? I'm not sure what the best approach is here.
to know how to do this watch my video
https://1drv.ms/v/s!AiUZ0Ws7G26RjGrE5atu46KqC_bh?e=3oz8WT
= Table.ReplaceValue( Custom1 , each [victim], each if [victim] is text then [ [victim]] else [victim], Replacer.ReplaceValue,{"victim"})
7 Replies
- Ahmedx
Super User
to know how to do this watch my video
https://1drv.ms/v/s!AiUZ0Ws7G26RjGrE5atu46KqC_bh?e=3oz8WT
= Table.ReplaceValue( Custom1 , each [victim], each if [victim] is text then [ [victim]] else [victim], Replacer.ReplaceValue,{"victim"}) - amitchandak
TP-101 , You have to take approach suggested in this power query solution
https://www.myonlinetraininghub.com/extracting-data-from-lists-and-records-in-power-query
TP-101Thank you for the tip amitchandak . I gave that a try and couldnt get it to work the way I want. Let me rephrase the original ask. The column I want to expand "victim", if it says "Any", I do want to keep that value and also expand the Record. The data source is JSON so I'm trying to unpack the nested layers. I'm working with the Advanced Editor, any code examples would be helpful
- DallasBaba
Skilled Sharer
TP-101 You can filter out the rows with "Any" in the Victim Column before expanding the Column data
You don't have to use code, but this is a coding sample. ( Change the Datasource in line 2 to your TableName )
let Source = <Datasource>, #"Filtered Rows" = Table.SelectRows(Source, each ([<Victim>] <> "Any")), #"Expanded Column" = Table.ExpandRecordColumn(#"Filtered Rows", "<Victim >", {"<Record>"}) in #"Expanded Column"Let me know if this works for you. @ me in replies, or I'll lose your thread!!!
Note:If this post helps, please consider Accepting it as the solution to help others find it more quickly.Best Regards,Dallas.- TP-101
Hi DallasBaba ,
I tried this approach and observing a different Expression Error: The import victem matches no exports. Did you miss a module reference?
This is the code I'm using, not exactly what you recommended, do you know what needs to be changed?
#"Filtered Rows" = Table.SelectRows(victim, each (victim <> "Any")), #"Expanded Column" = Table.ExpandRecordColumn(#"Filtered Rows", "<Victim >", {"<Record>"}) in #"Expanded Column"- DallasBaba
TP-101 Please modify "<Victim >" in line #4 to "Victim" without <>
let Source = <Datasource>, #"Filtered Rows" = Table.SelectRows(Source, each ([Victim] <> "Any")), #"Expanded Column" = Table.ExpandRecordColumn(#"Filtered Rows", "Victim", {"Record"}) in #"Expanded Column"Or use it as it's in line #3 [<Victim>]
#"Filtered Rows" = Table.SelectRows(Source, each ([<Victim>] <> "Any")), #"Expanded Column" = Table.ExpandRecordColumn(#"Filtered Rows", "[<Victim>]", {"<Record>"})Or
#"Filtered Rows" = Table.SelectRows(source, each ([#"Victim" <> "Any"])), #"Expanded Column" = Table.ExpandRecordColumn(#"Filtered Rows", "#""Victim""", {"<Record>"})I hope these suggestions are helpful to you. Give it a kudos by clicking the Thumbs Up! OR Accept it as the solution to help the other members find it more quickly.Best Regards,Dallas.
