Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
jo123456
Frequent Visitor

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

 

jo123456_0-1717352843977.pngthis is how it looks per row 

and this is how I want it to look

desired output 

jo123456_1-1717353093439.png

 

let me know if you need a sample table 

 

1 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.

View solution in original post

7 REPLIES 7
v-xinruzhu-msft
Community Support
Community Support

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

vxinruzhumsft_0-1717479418448.png

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.

 

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

@v-xinruzhu-msft, 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.

thank you so much

lbendlin
Super User
Super User

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...

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors