Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi all
I have this excel file (as an example)
Arrays values in Age and Length columns are related ..
I want inside power Query to extract these values and get the follwoing table :
How could I implemnt that !
thanks in advance
Regards
Hi @Nazdac911 ,
In Power Query, please open a blank query --> Advanced editor -->Remove any existing code and copy and paste the below code.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("FYqxDYAwDMBesTIzJIXQ9gIkXoiyMZP/N8pgWbIcIXe9sslV9SyFYjR2DpyTzsi/dqU7QxnOVKZjuk61RUvJ/AA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"First Name" = _t, #"Last Name" = _t, #"Age (Year)" = _t, #"Length (CM)" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"First Name", type text}, {"Last Name", type text}, {"Age (Year)", type text}, {"Length (CM)", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type","[","",Replacer.ReplaceText,{"Age (Year)", "Length (CM)"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","]","",Replacer.ReplaceText,{"Age (Year)", "Length (CM)"}),
#"Added Custom" = Table.AddColumn(#"Replaced Value1", "Custom", each Table.FromColumns({Text.Split([#"Age (Year)"], " "), Text.Split([#"Length (CM)"], " ")})),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Age (Year)", "Length (CM)"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Column1", "Column2"}, {"Age (Year)", "Length (CM)"})
in
#"Expanded Custom"
Input
Output
Kind regards,
Rohit
Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 🙂
Thanks a lot
Could you mention the steps in power Query to genetrate this code ..
What I added is an example table , the real one in 15 columns with different data types 🙂
thanks a again
Regards