Forum Discussion

Nazdac911's avatar
Nazdac911
Helper II
3 years ago
Solved

In Power Query , how to Extract values from many Arrays columns

Hi all  I have this excel file (as an example) ( the original data is much bigger and had 15 columns  Arrays values in Age and Length columns are related ..  I want inside power Query to ext...
Need Help
Tips and Tricks
  • amitchandak's avatar
    amitchandak
    3 years ago

    Nazdac911 , convert them to list by replace [ -> { and ] -> }  and space with comma

     

    then use List.Zip to zip the columns into a new column

     

    Find the code below

    Please check steps after custom column, attaching the file used

     

     

    let
    Source = Excel.Workbook(File.Contents("C:\Amit Files\PBI two list.xlsx"), null, true),
    Data_Sheet = Source{[Item="Data",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Data_Sheet, [PromoteAllScalars=true]),
    #"Added Custom" = Table.AddColumn(#"Promoted Headers", "Zip", each let

_break = List.Zip( {Text.Split([Subject],","), Text.Split([Marks],",")} ),
_Trans = List.Transform(_break, each Record.FromList(_, {"Subject", "Marks"}))
in 
_Trans),
    #"Expanded Zip" = Table.ExpandListColumn(#"Added Custom", "Zip"),
    #"Expanded Zip1" = Table.ExpandRecordColumn(#"Expanded Zip", "Zip", {"Subject", "Marks"}, {"Zip.Subject", "Zip.Marks"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Zip1",{"Subject", "Marks"})
in
    #"Removed Columns"

     

     

     

amitchandak's avatar
amitchandak
Super User
3 years ago

Nazdac911 , convert them to list by replace [ -> { and ] -> }  and space with comma

 

then use List.Zip to zip the columns into a new column

 

Find the code below

Please check steps after custom column, attaching the file used

 

 

let
    Source = Excel.Workbook(File.Contents("C:\Amit Files\PBI two list.xlsx"), null, true),
    Data_Sheet = Source{[Item="Data",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Data_Sheet, [PromoteAllScalars=true]),
    #"Added Custom" = Table.AddColumn(#"Promoted Headers", "Zip", each let

_break = List.Zip( {Text.Split([Subject],","), Text.Split([Marks],",")} ),
_Trans = List.Transform(_break, each Record.FromList(_, {"Subject", "Marks"}))
in 
_Trans),
    #"Expanded Zip" = Table.ExpandListColumn(#"Added Custom", "Zip"),
    #"Expanded Zip1" = Table.ExpandRecordColumn(#"Expanded Zip", "Zip", {"Subject", "Marks"}, {"Zip.Subject", "Zip.Marks"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Zip1",{"Subject", "Marks"})
in
    #"Removed Columns"