Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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"
Hi @Ylreeb,
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
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