Forum Discussion

jo123456's avatar
jo123456
Icon for Helper I rankHelper I
2 years ago
Solved

Power 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...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi
lbendlin ,Thanks for your concern about the problem, and i want to offer some more information for user to refer to.

hello jo123456 , you can create a blank query and put the following code to advanced editor in power query.

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.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"installed_packages", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "installed_packages"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"installed_packages", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type1", "Index", 1, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each let 
a =List.Max(Table.SelectRows(#"Added Index",(x)=>x[Index]=[Index]+1)[installed_packages]),
b=if Text.StartsWith(a," ") then [installed_packages]&"  "&Text.TrimStart(a) else  [installed_packages]
in try  b otherwise null),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each (not Text.StartsWith([installed_packages], " ")) and Text.Select([installed_packages],{"0".."9"})<>""),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"installed_packages", "Index"})
in
    #"Removed Columns"

Output

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's avatar
    jo123456
    Icon for Helper I rankHelper I
    2 years ago

    this seems like it worked but the data is taking a long time to load. I will accept it the solution though

    • jo123456's avatar
      jo123456
      Icon for Helper I rankHelper I
      2 years ago

      Anonymous, 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

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        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.