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
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
Solved! Go to 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.
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.
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
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...
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
72 | |
67 | |
24 | |
18 | |
13 |