Forum Discussion
Anonymous
4 years agoNot applicable
Convert string into array in power BI
Hello All, I have a scenario to convert a field of data type varachar "03/28 8:00-7:30 03/29 8:00-7:30 03/30 8:00-6:00 04/02 8:00-7:30 04/03 8:00-7:30 04/04 8:00-7:30 04/05 8:00-7:30 04/06 8:00-7:30...
- 4 years ago
There are a lot more elegant functions to do that but this one here is fast and nimble.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xc5LCoAwDEXRrZSMlSZN7W9WcRel+9+GURzYN7lwCIQ3Bglt1M/LyupDcaUx77kpu8d1tfV1sjiOnsP/blZwBB/gBM7wv653YbCAYY/AHvn2qIXmvAE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Emp_ID = _t, Emp_Name = _t, Column3 = _t]), #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Column3", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column3"), #"Added Index" = Table.AddIndexColumn(#"Split Column by Delimiter", "Index", 0, 1, Int64.Type), #"Added Custom" = Table.AddColumn(#"Added Index", "date_hrs_operation", each if Number.IsEven([Index]) then [Column3] & " " & #"Split Column by Delimiter"[Column3]{[Index]+1} else null), #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([date_hrs_operation] <> null)), #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Emp_ID", "Emp_Name", "date_hrs_operation"}) in #"Removed Other Columns"How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".
lbendlin
Super User
4 years agoThere are a lot more elegant functions to do that but this one here is fast and nimble.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xc5LCoAwDEXRrZSMlSZN7W9WcRel+9+GURzYN7lwCIQ3Bglt1M/LyupDcaUx77kpu8d1tfV1sjiOnsP/blZwBB/gBM7wv653YbCAYY/AHvn2qIXmvAE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Emp_ID = _t, Emp_Name = _t, Column3 = _t]),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Column3", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column3"),
#"Added Index" = Table.AddIndexColumn(#"Split Column by Delimiter", "Index", 0, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "date_hrs_operation", each if Number.IsEven([Index]) then [Column3] & " " & #"Split Column by Delimiter"[Column3]{[Index]+1} else null),
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([date_hrs_operation] <> null)),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Emp_ID", "Emp_Name", "date_hrs_operation"})
in
#"Removed Other Columns"