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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Belgarion42
Frequent Visitor

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):

 

Belgarion42_0-1728429412155.png

 

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?

1 ACCEPTED SOLUTION
PhilipTreacy
Super User
Super User

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:

PhilipTreacy_0-1728431737985.png

 

After:

PhilipTreacy_1-1728431759663.png

 

Regards

 

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

2 REPLIES 2
PhilipTreacy
Super User
Super User

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:

PhilipTreacy_0-1728431737985.png

 

After:

PhilipTreacy_1-1728431759663.png

 

Regards

 

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.