Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Whenever 12345 appears in column [UPC] insert a new blank row, directly underneath it.
I would love to see this in M code. I use the Advanced Editor in PowerQuery to do my work.
Solved! Go to Solution.
Hi spar101
You could try below query
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("PcqxDQBACELRXahtRLcx7L/GmdPY/cCrgjMSBoesQHbyZ3TFjHev5c0c4WdXJKQH", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [column1 = _t, amount = _t]), #"Added Custom" = Table.AddColumn(Source, "Custom", each if [column1] = "1234" then {"1234",""} else {[column1]}), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"column1"}), #"Expanded Custom" = Table.ExpandListColumn(#"Removed Columns", "Custom"), #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Custom.1", each if [Custom] ="" then "" else [amount]), #"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"amount"}) in #"Removed Columns1"
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi spar101
You could try below query
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("PcqxDQBACELRXahtRLcx7L/GmdPY/cCrgjMSBoesQHbyZ3TFjHev5c0c4WdXJKQH", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [column1 = _t, amount = _t]), #"Added Custom" = Table.AddColumn(Source, "Custom", each if [column1] = "1234" then {"1234",""} else {[column1]}), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"column1"}), #"Expanded Custom" = Table.ExpandListColumn(#"Removed Columns", "Custom"), #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Custom.1", each if [Custom] ="" then "" else [amount]), #"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"amount"}) in #"Removed Columns1"
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
I think there are other ways to accomplish this but I could get it by playing with index column and list function.
Check my code:
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Replaced Value" = Table.ReplaceValue(Source,"line","12345",Replacer.ReplaceText,{"UPC"}), #"Added Index" = Table.AddIndexColumn(#"Replaced Value", "Index", 1, 1), #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if [UPC] = "12345" then [Index]+1 else [Index]), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each {[Index]..[Custom]}), #"Expanded Custom.1" = Table.ExpandListColumn(#"Added Custom1", "Custom.1"), #"Added Custom2" = Table.AddColumn(#"Expanded Custom.1", "UPC2", each if [Index] <> [Custom.1] then null else [UPC]), #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"UPC", "Index", "Custom", "Custom.1"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"UPC2", "UPC"}}), #"Changed Type10" = Table.TransformColumnTypes(#"Renamed Columns",{{"UPC", type text}}) in #"Changed Type10"
Just change your Source step and check if you get what you need.
If you have any question, just let me know.
Regards,
Gian Carlo Poggi
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
53 | |
21 | |
13 | |
11 |