How to Remove Changing Values

[Belgarion42]

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?

[PhilipTreacy · Accepted solution]

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

[Belgarion42]

Perfect! That's exactly what I needed, thank you.