Power Query Help

[jo123456]

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 break but I'm running to issues where some packages and their repos are broken off to different lines.  I just need help solution. 

this is how it looks per row 

and this is how I want it to look

desired output 

let me know if you need a sample table 

[Anonymous]

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]

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

[jo123456]

@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 · Accepted solution]

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

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[jo123456]

link to file