Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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 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!!

Need Help
  • lbendlin's avatar
    lbendlin
    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".

1 Reply

  • lbendlin's avatar
    lbendlin
    Super User
    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".