Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

How filter out Values stored within an mixed up Expand Column

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"

Need Help

2 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      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