Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 04/07 8:00-6:00 04/09 8:00-7:30 04/10 8:00-7:30 04/11 8:00-7:30
04/12 8:00-7:30 04/13 8:00-7:30 04/14 8:00-3:00"
into an array using power BI? in power BI desktop or Power query?
Data now looks like
Emp_ID Emp_name date_hrs_operation
1 ABD "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 04/07 8:00-6:00 04/09 8:00-7:30 04/10 8:00-7:30 04/11 8:00-7:30
04/12 8:00-7:30 04/13 8:00-7:30 04/14 8:00-3:00"
I need to be in this format,
Emp_ID Emp_Name date_hrs_operation
1 ABD 03/28 8:00-7:30
1 ABD 03/29 8:00-7:30
1 ABD 03/30 8:00-6:00
1 ABD 04/02 8:00-7:30
etc.. By any method can I achieve this?
Thanks in advance!!
Solved! Go to Solution.
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"
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"