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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
limewire
Helper I
Helper I

Howto denote unstable data/create a conditional column

Really hoping someone can help me out with this one!

This is what I currently have after I have "unbundled" the sections. Each element has 5 parts, unwgtd, weighted, horz %, vert % and will denote with an "*" if the data is unstable. So for row 5 - 9, "Column 1" is constant and "All" column row 5 would have a "*" if the data was unstable for the elements that follow in row 6, 7, 8 or 9.

help.PNG

Row 20  has "*" in the "All" column. How do I have that "*" also attach to row 21,22,23 &24? 

Is it possible for every element in these mini groupings to have the "*" ?

 

Another idea I had was potentially making a column that would assign the "*" to the value in "Column 1" in a way that would allow for the other elements to be present as well. 

 

I've tried making a table that could be connected to this one that would function as a"key" but have had no such luck.

 

Any and all help is great appreciated! 

 

3 ACCEPTED SOLUTIONS
v-cherch-msft
Employee
Employee

Hi @limewire 

You may add an index column first.Then use fill down and condition columns to get it.For example:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUQrNK08vSQEyDJVidSBC4amZ6RklqSkKzvmleSVAASO4lEd+UZWCKpBhDBcKSy0qAQtBVCUBWUCkBefALUDIY1hgDJeCW2ACF4JbYIxsAab5JrjNh0glI5tvChdC80AysgeSkS0whgthWGCKaYExpgWGyBYoxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, ALL = _t]),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Index",{{"Column1", type text}, {"Column2", type text}, {"ALL", type text}, {"Index", Int64.Type}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if [ALL] = "*" then [Index] else null),
    #"Filled Down" = Table.FillDown(#"Added Conditional Column",{"Custom"}),
    #"Added Conditional Column2" = Table.AddColumn(#"Filled Down", "Custom.2", each if [Custom] = null then null else if [Index] <= [Custom] +4then "*" else null)
in
    #"Added Conditional Column2"

Regards,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

@v-cherch-msft I'd like to add that it might not be necessary for the use of the asterisk, if you know another way to properly denote these unstable values (that follow the pattern of the conditional column you properly created prior), please let me know! 

View solution in original post

Hi @limewire 

You may change the number of decimal places as below.If you have other questions,I would suggest you create a new thread on forum so that more community members can see it and provide advice. Please remember to post dummy data and desired result.

1.png

Regards, 

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
v-cherch-msft
Employee
Employee

Hi @limewire 

You may add an index column first.Then use fill down and condition columns to get it.For example:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUQrNK08vSQEyDJVidSBC4amZ6RklqSkKzvmleSVAASO4lEd+UZWCKpBhDBcKSy0qAQtBVCUBWUCkBefALUDIY1hgDJeCW2ACF4JbYIxsAab5JrjNh0glI5tvChdC80AysgeSkS0whgthWGCKaYExpgWGyBYoxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, ALL = _t]),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Index",{{"Column1", type text}, {"Column2", type text}, {"ALL", type text}, {"Index", Int64.Type}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if [ALL] = "*" then [Index] else null),
    #"Filled Down" = Table.FillDown(#"Added Conditional Column",{"Custom"}),
    #"Added Conditional Column2" = Table.AddColumn(#"Filled Down", "Custom.2", each if [Custom] = null then null else if [Index] <= [Custom] +4then "*" else null)
in
    #"Added Conditional Column2"

Regards,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-cherch-msft  Thank you so very much for your response!!! 

 

I tried my best to follow your instructions but still have not succeeded in my mission.

Following your instructions, I was able to get the pattern I requested. Using that, in the data model view, I added a calculated column that was able to combine the symbols ("Custom 1" column) to the values ("All" column). Yet when I attempted to rank it by the calculated column (I named the column Sigfig"), it is clear that it is not in the proper order.

 

Is there any way to rectifyy this?

help 3.0.PNG

 

And is there a way to make the numbers rounded to the nearest hundredths place?

 

Again thank you so much for your help!!

@v-cherch-msft I'd like to add that it might not be necessary for the use of the asterisk, if you know another way to properly denote these unstable values (that follow the pattern of the conditional column you properly created prior), please let me know! 

Hi @limewire 

You may change the number of decimal places as below.If you have other questions,I would suggest you create a new thread on forum so that more community members can see it and provide advice. Please remember to post dummy data and desired result.

1.png

Regards, 

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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