Forum Discussion

TP-101's avatar
TP-101
Icon for Helper I rankHelper I
2 years ago
Solved

Expanding 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 th...
Need Help
DallasBaba's avatar
DallasBaba
Icon for Skilled Sharer rankSkilled Sharer
2 years ago

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's avatar
TP-101
Icon for Helper I rankHelper I
2 years ago

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's avatar
    DallasBaba
    Icon for Skilled Sharer rankSkilled Sharer
    2 years ago

    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.