Extract value from a column to make it another column?

[Anonymous]

Hello,

I would like some help with a report I am building.

I want to make a rate graphic with a line for the constraint
My base structure is this one:

  

After I connected to it I selected the first two columns and unpivoted the other ones and then my base turned into this:

Is there a way for me to extract the constraint "family" so I can make another column named constraint to use it as a line in the graphic? 

Thanks!

[MFelix]

Hi @Anonymous ,

I believe there is no need to make a new column when using the values on a visualization you can do a filter on the family field to show only the Constraint or to filter out the 1,2,3.

[Anonymous]

Hi @MFelix ,

Sorry if I'm missing some features but is there a way to filter the columns vlaues to show no constraints and the line values to show the constraint in the same visualization?

So I could have the rates in the column values and the Constraint value in the line?

[MFelix]

Hi @Anonymous ,

Yes you can do two measures something similar to this:

Constraints = CALCULATE ( SUM(Table[Value]); Table[Family] in {"1- constraints", "2- constrains"})

Not Constraints = SUM(Table[Value]) - [Contraints]

Then use this measures as your lines and columns, be aware that you need to add the rest of the contraints on the in clause. 

[Anonymous]

@MFelix , I don't think I am making myself clear to what I am looking for.

I want to build a chart with the Family value in the column series and the constraint values in the line series.

With that, I want to have a slicer so I can have the value of all families together with their constraints but I still could select the family to have the its value with its constraint.

Probably should use a new column and not a measure, right?

The idea is to extract the values whose Family values contains " 1 - constraint" ; "2 - constraint" .... to a new column called "Constraint" that will show the value of the constraint for each row that match the family "1"; "2"; ....

Is there a way?

Thanks

[MFelix · Accepted solution]

Hi @Anonymous ,

Just to confirm a question about your data when you present 1 - Constraint this is the constraint of family 1?

If yes I believe you need to do a different treatment I would do the steps below (be aware that this is based on that you have the information with 1 - constraint, and so on):

• Filter all rows that aren't constraints
• Unpivot the values
• Rename the column value
• Insert a step refering to the Source step
• Filter all rows that are constraints
• Unpivot
• Rename the column value to constraints
• Replace the Constraint in family column in order to have only the family (1 , 2, ,3, 4)
• Merge queries the one after the renaming the values with the previous one
  • Should be merged by 3 values: family, year, and attribute
• Expand the column contstraints

Check code below and PBIX file attached.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIyMDIAUqZGQMLcQilWJ1rJCCFsBmKbgEWNkUQtgYSxKVjYBEkYogEkaqigq5Ccn1dcUpSYmVeCZI0pkLCE2IJDiQXIRIhDjHEoMTM1AVsHth+HGpAKQ6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [family = _t, Year = _t, jan = _t, Feb = _t]),
    Filter_Constrains_Out = Table.SelectRows(Source, each not Text.Contains([family], "constraint")),
    Unpivot_Values = Table.UnpivotOtherColumns(Filter_Constrains_Out, {"family", "Year"}, "Attribute", "Value"),
    Rename_Column_Value = Table.RenameColumns(Unpivot_Values,{{"Value", "Family_Values"}}),
    Retrieve_Source = Source,
    Filter_Only_Constraints = Table.SelectRows(Retrieve_Source, each Text.Contains([family], "constraint")),
    Unpivot_Constrainst = Table.UnpivotOtherColumns(Filter_Only_Constraints, {"family", "Year"}, "Attribute", "Value"),
    Rename_Constraints = Table.RenameColumns(Unpivot_Constrainst,{{"Value", "Constraint"}}),
    Replace_Constrains_text = Table.ReplaceValue(Rename_Constraints," - constraint","",Replacer.ReplaceText,{"family"}),
    Merge_RenameColumnValue_ReplaceConstrainsText = Table.NestedJoin(Rename_Column_Value, {"family", "Year", "Attribute"}, Replace_Constrains_text, {"family", "Year", "Attribute"}, "Replaced Value", JoinKind.LeftOuter),
    Expand_Constraint = Table.ExpandTableColumn(Merge_RenameColumnValue_ReplaceConstrainsText, "Replaced Value", {"Constraint"}, {"Constraint"})
in
    Expand_Constraint

[Anonymous]

Hi @MFelix ,

Yes, exactly!

I think your solution is the one I need since I could get into your pbi file.

I just had some problems to use it since I couldn't find how to do the step "Insert a step refering to the Source step", is it just by coding or there is a button for it?

Thanks

[MFelix]

Rigth click on the last step you have and then insert step then on the formula bar just write =Source.

[Anonymous]

@MFelix 
It worked super well!

Thank You very much!