The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
99 | |
93 | |
81 | |
63 | |
56 |
User | Count |
---|---|
248 | |
124 | |
111 | |
78 | |
78 |