How filter out Values stored within an mixed up Expand Column

[Anonymous]

Dear All,

sure it sounds pretty simple, but it's robbing me of the last nerve...

I'm trying to filter out text and number values from a duplicate/ custom column storing also table values (see picture).

I already determined to use some kind of M code like "each if Value.Is" in order to set "table" values to "null" and just tacking over text/numbers....but I'm struggling with the M-syntax...

Thx in advance

Here my code so far:

let
    Source = Oracle.Database("localhost:1525/balabla", [HierarchicalNavigation=true]),
    ARDOME = Source{[Schema="Vendor"]}[Data],
    ITEM1 = ARDOME{[Name="ITEM"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(ITEM1,{{"ITM_ID", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([ITM_REVISION] <> 1) and ([ITM_MEDIA_STATUS] = "deleting" or [ITM_MEDIA_STATUS] = "offline" or [ITM_MEDIA_STATUS] = "online")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"ITM_REVISION", "ITM_EXT_ID_STR", "ITM_EXT_ID_SRC", "ITM_EXT_UPDATE_TS", "ITM_TITLE", "ITM_FILE_NAME", "ITM_CREATE_BY", "ITM_CREATE_TS", "ITM_UPDATE_TS", "ITM_UPDATE_BY", "ITM_USER_UPDATE_TS", "ITM_USER_UPDATE_BY", "ITM_ACL_ID", "ITM_MEDIA_ACL_ID", "ITM_ARCHIVE_BY", "ITM_ARCHIVE_TS", "ITM_CATEGORY", "ITM_RIGHTS_CODE", "ITM_PREFERRED_MST_ID", "ITM_ALTERNATE_URI", "ITM_OVERLAY_URI", "ITM_DELETE_TS", "ITM_MEDIA_CACHE", "ITM_KEYWORD_CACHE", "ITM_LANG_CACHE", "ITM_QC_STATUS", "ITM_TX_STATUS", "ITM_MEDIA_STATUS", "ITM_INGEST_TS", "ITM_INGEST_START_TS", "ITM_BLOCKED_TS", "ITM_RETENTION_DATE", "ITM_PURGE_RANK", "ITM_POSTER_MOB_ID", "ITM_POSTER_START_MS", "ITM_POSTER_URL", "ITM_KEYFRAME_PATH", "ITM_ASPECT_RATIO", "ITM_ROTATION", "ITM_AFD", "ARDOME.ACL(ITM_ACL_ID)", "ARDOME.ACL(ITM_MEDIA_ACL_ID)", "ARDOME.AVAILABILITY", "ARDOME.CLIP_MAPPING", "ARDOME.EDL", "ARDOME.INCOMING_MEDIA", "ARDOME.INCOMING_MEDIA_MIN", "ARDOME.ITEM_SET_MEMBER", "ARDOME.KEYFRAME_INFO", "ARDOME.LOG_TRACK", "ARDOME.LOG_TRACK_ITEM", "ARDOME.MIN", "ARDOME.MOB(ITM_ID)", "ARDOME.MOB(ITM_POSTER_MOB_ID)", "ARDOME.MOB_SET(ITM_ID)", "ARDOME.MOB_SET(ITM_PREFERRED_MST_ID)", "ARDOME.MOS_OBJECT", "ARDOME.OVERLAY_TIMELINE", "ARDOME.PHYSICAL_METADATA", "ARDOME.QC_CONTEXT", "ARDOME.STAGE_ENTRY", "ARDOME.TAPE_ENTRY", "ARDOME.TRANSCODE_REPORT", "ARDOME.XFER_MEDIA", "ARDOME.XFER_REQUEST"}),
    #"Parsed XML" = Table.TransformColumns(#"Removed Columns",{{"ITM_METADATA", Xml.Tables}}),
    #"Removed Errors" = Table.RemoveRowsWithErrors(#"Parsed XML", {"ITM_METADATA"}),
    #"Expanded ITM_METADATA" = Table.ExpandTableColumn(#"Removed Errors", "ITM_METADATA", {"field"}, {"ITM_METADATA.field"}),
    #"Filtered Rows1" = Table.SelectRows(#"Expanded ITM_METADATA", each ([ITM_ID] = "2371711290000007521" or [ITM_ID] = "2371712060000010521" or [ITM_ID] = "2371712060000010821" or [ITM_ID] = "2371801050000015221" or [ITM_ID] = "2371801090000016921" or [ITM_ID] = "2371801090000017021" or [ITM_ID] = "2371801090000017121" or [ITM_ID] = "2371801100000017221" or [ITM_ID] = "2371801100000017321" or [ITM_ID] = "2371801100000017421" or [ITM_ID] = "2371801110000019321" or [ITM_ID] = "2371802010000029621")),
    #"Expanded ITM_METADATA.field" = Table.ExpandTableColumn(#"Filtered Rows1", "ITM_METADATA.field", {"value", "Attribute:name"}, {"ITM_METADATA.field.value", "ITM_METADATA.field.Attribute:name"}),
    #"Filtered Rows3" = Table.SelectRows(#"Expanded ITM_METADATA.field", each true),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows3", "Just_TextValues", each [ITM_METADATA.field.value])
in
    #"Added Custom"

[v-yulgu-msft]

Hi @Anonymous,

Please check whether this thread is helpful to your scenario:

Filtering a Power Query Table Based on a Column That's a Table

Best regards,

Yuliana Gu

[Anonymous]

Hi Yuliana Gu,

your posted thread covers not my scenario - it discusses some even more complicated...

At the end I just want to filter out the "Table" values"....or (inverted) the "hello world" value  (in the given example) by replacing to "null"

Therefore I tried:

-by adding a new column

#"Added Custom" = Table.AddColumn(#"Filtered Rows3", "Just_TextValues", each not ([ITM_METADATA.field.value] = type tables)),

-filtering

#"Filtered Rows2" = Table.SelectRows(#"Added Custom", each ([#"Just_TextValues"] <> type table))

-replacing values

Also tried to set the column data type by  "Value.ReplaceType(#"Duplicated Column", type text)" or

to replace the "Table" value by Table.ReplaceValue(#"Duplicated Column", "Table",null,Replacer.ReplaceValue,{"Just_Text"})

but I could not figure out how to address a "Table" value in this case

The reason doing this are differing XML's stored as BLOB-data (http://community.powerbi.com/t5/Desktop/Query-on-differing-XML-BLOB-values/td-p/384882) 

Regards and thx in advance,

Marcus