Forum Discussion
Extract ID from textstring
You could try splitting it by space and only select items starting with ID?
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSspJBCIFBJUDoj1dTEzNoIJApBSrg6YSpsocRVUsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Split([Column1]," ")), #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"), #"Filtered Rows" = Table.SelectRows(#"Expanded Custom", each Text.StartsWith([Custom], "ID")) in #"Filtered Rows"
You can do it in dax.
Here is basic formulas for a new column in a table. You would need to add error handling for data that does not comply to the example you gave.
Find ID = find("ID",Table14[Column1])Find Space After ID = find(" ",Table14[Column1],Table14[Find ID])ID = mid(Table14[Column1],Table14[Find ID],Table14[Find Space After ID]-Table14[Find ID])
You should be able to do the same in the Edit queries section as well. It works for those examples but you may find not all the data complies to this pattern.
Thanks!
Sorry however if I wasnt clear, I need the function to work in power query. This is a loading table to another query at the end of the process
You could try something that splits the text into rows based on the spaces but only selects rows that begin "ID" for example.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSspJBCIFBJUDoj1dTEzNoIJApBSrg6YSpsocRVUsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Split([Column1]," ")), #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"), #"Filtered Rows" = Table.SelectRows(#"Expanded Custom", each Text.StartsWith([Custom], "ID")) in #"Filtered RowsYou could try splitting it by space and only select items starting with ID?
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSspJBCIFBJUDoj1dTEzNoIJApBSrg6YSpsocRVUsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Split([Column1]," ")), #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"), #"Filtered Rows" = Table.SelectRows(#"Expanded Custom", each Text.StartsWith([Custom], "ID")) in #"Filtered Rows"