Forum Discussion
How to Remove Changing Values
I have the following data that comes into Power BI from an Excel spreadsheet, which a user updates every week (current week provided as the example):
The top row will not change until Feb 1 next year. For the other 3 rows, the week # changes every week. The next step in my Power Query transformation is to transpose the data so that first column becomes the row headers which needs to be as follows:
Annual Commitment
Target number
Actual number
Completion Percentage
Once it has been transposed I can rename the columns to remove the characters I don't need, but that puts the exact current text in the Advanced Editor, and when "Week 34" changes next week to "Week 35", it will break.
How can I generalize the transformation so that as the week # changes, it still works? Or is that even possible?
Hi Belgarion42
Download PBIX file with the example below
This code will do the job
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcszLK03MUXDOz83NLMlNzStR0DAw0jcw1DcyUdBVANLGQKapppKOkpmZgYFSrE60UnhqaraCsYlCSGJRemqJgl9pblJqEVCBiaGFEYoCx+QSkNkIBSYWEBOAthXkpJZk5ucpBKQWJQNtTUxPBVoH1QhUaqBnZm4BhpZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Annual Commit" = _t, Column2 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Annual Commit", type text}, {"Column2", type number}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let _words = Text.Split([Annual Commit], " ") in if Text.Contains([Annual Commit], "Annual") then _words{0} & " " & _words{1} else if Text.Contains([Annual Commit], "Target") then _words{2} & " " & _words{3} else if Text.Contains([Annual Commit], "Actual") then _words{2} & " " & _words{3} else if Text.Contains([Annual Commit], "Completion") then _words{0} & " " & _words{1} else [Annual Commit]), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Annual Commit"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Annual Commit"}}), #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Annual Commit", "Column2"}) in #"Reordered Columns"Before:
After:
Regards
Phil
2 Replies
Hi Belgarion42
Download PBIX file with the example below
This code will do the job
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcszLK03MUXDOz83NLMlNzStR0DAw0jcw1DcyUdBVANLGQKapppKOkpmZgYFSrE60UnhqaraCsYlCSGJRemqJgl9pblJqEVCBiaGFEYoCx+QSkNkIBSYWEBOAthXkpJZk5ucpBKQWJQNtTUxPBVoH1QhUaqBnZm4BhpZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Annual Commit" = _t, Column2 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Annual Commit", type text}, {"Column2", type number}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let _words = Text.Split([Annual Commit], " ") in if Text.Contains([Annual Commit], "Annual") then _words{0} & " " & _words{1} else if Text.Contains([Annual Commit], "Target") then _words{2} & " " & _words{3} else if Text.Contains([Annual Commit], "Actual") then _words{2} & " " & _words{3} else if Text.Contains([Annual Commit], "Completion") then _words{0} & " " & _words{1} else [Annual Commit]), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Annual Commit"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Annual Commit"}}), #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Annual Commit", "Column2"}) in #"Reordered Columns"Before:
After:
Regards
Phil
Perfect! That's exactly what I needed, thank you.
