Forum Discussion
jo123456
Helper I
Helper IPower Query Help
I posted the message here but it got deleted. I need some help with Power Query. I have data per row value that has installed packages information with line breaks. I need to create new rows per line...
Hi jo123456
You can try the following code.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZJNawMhEIb/iuRcB1bzferSQwmkdKEttGSDGNfsSl2V1aVJf33Nps0nveT1ojLPzDuji0VvbnkhC+R0Wyrjp0huQsM9Q74tS+mDssbfodxobkrHxWc8uEbZRgUl/V3rCh4krm0ocjMzPnCtY7IsBvJI5yb98mkpTYDNeMiGfdSJQFwTSiDBJKFx092qPzw3j9LOMng/IAQGu1gCvP42FBKgF0C2/Uif5mdFEAVyzZxCXOhzooMoDAgm/T2Grhmniss6SewGTw6FyCHbCei2obIGr+JknG2Cx95rVvMgKlZZHwyvJRjLG1HlBt0guhsREHxi+6hrH2K9Pu+CwDCOePD/tMqmXVHsRHzsotXS/7rt2GkkKE5gPNqRIPUIhLytj6Pus9eUPcS/8/yCR+yt+2meYba33VsufwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [installed_packages = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"installed_packages", type text}}), #"Split Column by Delimiter" = Table.TransformColumns(#"Changed Type", {{"installed_packages", each Text.AfterDelimiter(_, "Installed Packages")}}), Custom1 = Table.TransformColumns(#"Split Column by Delimiter", {{"installed_packages", Splitter.SplitTextByAnyDelimiter({"#(lf)", " "})}}), Custom2 = Table.TransformColumns(Custom1, {{"installed_packages", each Table.FromRows(List.Split(List.RemoveItems(_, {""}),3),{"package","version","status"})}}), #"Expanded installed_packages" = Table.ExpandTableColumn(Custom2, "installed_packages", {"package", "version", "status"}, {"package", "version", "status"}), #"Merged Columns" = Table.CombineColumns(#"Expanded installed_packages",{"package", "version", "status"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged") in #"Merged Columns"It can improve the performent.
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
jo123456Helper I
Helper IAnonymous, it's taking a long time to load but would you have any guidance in translating this code to sql so it's not so intensive on Power BI. Uisng Postgresql
Hi jo123456
You can try the following code.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZJNawMhEIb/iuRcB1bzferSQwmkdKEttGSDGNfsSl2V1aVJf33Nps0nveT1ojLPzDuji0VvbnkhC+R0Wyrjp0huQsM9Q74tS+mDssbfodxobkrHxWc8uEbZRgUl/V3rCh4krm0ocjMzPnCtY7IsBvJI5yb98mkpTYDNeMiGfdSJQFwTSiDBJKFx092qPzw3j9LOMng/IAQGu1gCvP42FBKgF0C2/Uif5mdFEAVyzZxCXOhzooMoDAgm/T2Grhmniss6SewGTw6FyCHbCei2obIGr+JknG2Cx95rVvMgKlZZHwyvJRjLG1HlBt0guhsREHxi+6hrH2K9Pu+CwDCOePD/tMqmXVHsRHzsotXS/7rt2GkkKE5gPNqRIPUIhLytj6Pus9eUPcS/8/yCR+yt+2meYba33VsufwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [installed_packages = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"installed_packages", type text}}),
#"Split Column by Delimiter" = Table.TransformColumns(#"Changed Type", {{"installed_packages", each Text.AfterDelimiter(_, "Installed Packages")}}),
Custom1 = Table.TransformColumns(#"Split Column by Delimiter", {{"installed_packages", Splitter.SplitTextByAnyDelimiter({"#(lf)", " "})}}),
Custom2 = Table.TransformColumns(Custom1, {{"installed_packages", each Table.FromRows(List.Split(List.RemoveItems(_, {""}),3),{"package","version","status"})}}),
#"Expanded installed_packages" = Table.ExpandTableColumn(Custom2, "installed_packages", {"package", "version", "status"}, {"package", "version", "status"}),
#"Merged Columns" = Table.CombineColumns(#"Expanded installed_packages",{"package", "version", "status"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged")
in
#"Merged Columns"It can improve the performent.
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- jo123456
thank you so much