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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Berean_50
Helper I
Helper I

How to replace values of dynamic columns using values from another column in different rows

This is what I intend to do:

 

  1. Given the following table:
    Berean_50_0-1639182810429.png

     

  2. I want to achieve the following result, by grouping the table by User Id, Name and Period Number, and transposing the rows of columns Category Status and  Category Requirement Met to columns:
    Berean_50_1-1639183030518.png
  3. The next step is to update the value of all the columns after Period Number according to the value Category Status and Category Requirements Met.  The challenge I'm facing is that these columns (eg. Category - Status) are created dynamically when the row is transposed. I can't refer to them by name. Instead the name should be Category Name + " - Status". 

  4. Here's a step that I tried hard-coding the column name:
    = Table.ReplaceValue(#"Appended Query",null, each [Category Status], Replacer.ReplaceValue, {"Audit - Status"})
    This works if I hard-code the column name {"Audit - Status"}. 

  5. My question is: how do I set the value of my dynamic columns by dynamically replacing the value of column name using the value of the Category Name column? Something like:
  6. = Table.ReplaceValue(#"Appended Query",null, each [Category Status], Replacer.ReplaceValue, {[Category Name] & " - Status"}) // this doesn't work
  7. I have attached the files I'm working with to make it easy.

    https://1drv.ms/x/s!AuHocvOERo6IhNl9pWhLKdaytFAvsQ?e=TYjrwv

     

    https://1drv.ms/u/s!AuHocvOERo6IhNl-PiqZfyLQVdAvyA?e=ysLiED

Perhaps the way I'm going about solving this issue is not the correct one. I'm open to suggestions. 

Thank you

1 ACCEPTED SOLUTION
Thingsclump
Resolver V
Resolver V

Hi @Berean_50 

 

I am bit confused about row number 3 in step 1 of yours. This where you have ID as 100 but name as Mary. But other than below code seems to work. I will wait for your feedback.

 

Input:

Thingsclump_0-1639244642494.png

 

Result: (i could not take screenshot with all columns)

Thingsclump_1-1639244739938.png

 

Code:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwUNJR8srPyANShkDsWJqSWQKkA1LzUjLz0oEsv3ylWB0sKl1TSpMTSzLzQXzn/NyCnNSS1BQgOzK1GKzBCKzBN7GoEkgZ4TMaQyWQmZ1aAlGDYXQsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"User ID" = _t, Name = _t, #"Period Number" = _t, #"Category Name" = _t, #"Category Status" = _t, #"Category Requiment Met" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"User ID", Int64.Type}, {"Name", type text}, {"Period Number", Int64.Type}, {"Category Name", type text}, {"Category Status", type text}, {"Category Requiment Met", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Category name with status", each [Category Name] & " - Status"),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each [Category Name] & " - Requirements met"),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Category Name"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[#"Category name with status"]), "Category name with status", "Category Status"),
#"Pivoted Column1" = Table.Pivot(#"Pivoted Column", List.Distinct(#"Pivoted Column"[Custom]), "Custom", "Category Requiment Met")
in
#"Pivoted Column1"

 

accpet it as solution if it works for you 

 

Thanks

thingsclump

www.thingsclump.com 

 

View solution in original post

2 REPLIES 2
Thingsclump
Resolver V
Resolver V

Hi @Berean_50 

 

I am bit confused about row number 3 in step 1 of yours. This where you have ID as 100 but name as Mary. But other than below code seems to work. I will wait for your feedback.

 

Input:

Thingsclump_0-1639244642494.png

 

Result: (i could not take screenshot with all columns)

Thingsclump_1-1639244739938.png

 

Code:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwUNJR8srPyANShkDsWJqSWQKkA1LzUjLz0oEsv3ylWB0sKl1TSpMTSzLzQXzn/NyCnNSS1BQgOzK1GKzBCKzBN7GoEkgZ4TMaQyWQmZ1aAlGDYXQsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"User ID" = _t, Name = _t, #"Period Number" = _t, #"Category Name" = _t, #"Category Status" = _t, #"Category Requiment Met" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"User ID", Int64.Type}, {"Name", type text}, {"Period Number", Int64.Type}, {"Category Name", type text}, {"Category Status", type text}, {"Category Requiment Met", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Category name with status", each [Category Name] & " - Status"),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each [Category Name] & " - Requirements met"),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Category Name"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[#"Category name with status"]), "Category name with status", "Category Status"),
#"Pivoted Column1" = Table.Pivot(#"Pivoted Column", List.Distinct(#"Pivoted Column"[Custom]), "Custom", "Category Requiment Met")
in
#"Pivoted Column1"

 

accpet it as solution if it works for you 

 

Thanks

thingsclump

www.thingsclump.com 

 

Thank you so much @Thingsclump 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors