Expanding a data column with mixed Text and Records

[TP-101]

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.  

[Ahmedx · Accepted solution]

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

[TP-101]

Hi @Ahmedx , this was the correct solution.  Thank you.

[DallasBaba]

@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.

[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-101]

Thank 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