Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I'm trying to create a new table with custom columns.
I have a table with projects and metrics. Each project metric is related to a stage. In the scenario where there are are more than one metric for one stage, i would need to consider the newest.
This is an example of project metrics
Project | Stage | date | Type | value |
Project1 | Certification | 01/01/2015 | Target | 1000 |
Project1 | Operation | 01/10/2018 | Actual | 800 |
Project1 | Close out | 01/10/2019 | Actual | 900 |
Project1 | Post close out | 01/01/2020 | Actual | 1100 |
Project1 | Post close out | 01/01/2021 | Actual | 1000 |
The new table would be something like:
Project | Target | Actual Operation (max value) | Actual Close out | Actual Post close out (max value) |
Project1 | 1000 | 800 | 900 | 1100 |
There is another consideration: Target metric for certification stage will always exists. If metric for close out stage does not exists, the value to consider until the close out metric is generated should be the target.
in the scenario where only target metric exists, the project metric table will be:
Project | Stage | date | Type | value |
Project1 | Certification | 01/01/2015 | Target | 1000 |
New table will be:
Project | Target | Estimated Close out |
Project1 | 1000 | 1000 |
Reading some of the related posts i was using the following dax for creating the new table with my target and Actual close out column, but it is only working for the target column.
BTI_NewMetrics = ADDCOLUMNS(SUMMARIZE(Opportunitymetrics, Opportunitymetrics[_nadb_opportunity_oppmt_value], Opportunitymetrics[nadb_measuretype_oppmt], Opportunitymetrics[nadb_metricstage_oppmt]),"Target",CALCULATE(sum(Opportunitymetrics[nadb_value_oppmt]),Opportunitymetrics[nadb_measuretype_oppmt]="Target"),"Actual Close out", CALCULATE(sum(Opportunitymetrics[nadb_value_oppmt]),FILTER(Opportunitymetrics,Opportunitymetrics[nadb_measuretype_oppmt]="Actual"),FILTER(Opportunitymetrics, Opportunitymetrics[nadb_metricstage_oppmt]=180830007)))
Note: Number 180830007 means 'Close out stage'
Thanks,
Solved! Go to Solution.
I have done this in Power Query as follows, copy and paste in a new query using Advanced Editor:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ldAxCsMwDAXQqwTPgUqGUmcsOUAzZAsZjFGLS6iKo9y/Sic7XlrQoD88kP40mSHxk4KgaU1PSeI9Bi+RX5oBTzoW8Kxh9OlBogsCgJnbQt7elDKFsCun4Rpk84surkb9wis1vEmOuhx1NRp4lSaU8nukhVwi/kOxoIf/7O/N2FoemgG3q0vVzPwB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [project = _t, stage = _t, date = _t, #"type" = _t, value = _t]),
merge_typeStage = Table.AddColumn(Source, "typeStage", each Text.Combine({[type], [stage]}, " "), type text),
groupTable = Table.Group(merge_typeStage, {"project", "typeStage"}, {{"data", each _, type table [project=text, stage=text, date=text, type=text, value=text, typeStage=text]}}),
addMaxValue = Table.AddColumn(groupTable, "maxValue", each Table.Max([data], "value")),
expandMaxValue = Table.ExpandRecordColumn(addMaxValue, "maxValue", {"value"}, {"value"}),
remDataCol = Table.RemoveColumns(expandMaxValue,{"data"}),
valueDataType = Table.TransformColumnTypes(remDataCol,{{"value", Int64.Type}}),
pivotCols = Table.Pivot(valueDataType, List.Distinct(valueDataType[typeStage]), "typeStage", "value", List.Sum),
addEstCloseOut = Table.AddColumn(pivotCols, "Estimated Close out", each if [Actual Close out] = null then [Target Certification] else null, Int64.Type)
in
addEstCloseOut
Which gives me the following output:
May not be EXACTLY as you wanted re: column names etc, but hopefully a strong startng point for adjustment.
Pete
Proud to be a Datanaut!
I have done this in Power Query as follows, copy and paste in a new query using Advanced Editor:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ldAxCsMwDAXQqwTPgUqGUmcsOUAzZAsZjFGLS6iKo9y/Sic7XlrQoD88kP40mSHxk4KgaU1PSeI9Bi+RX5oBTzoW8Kxh9OlBogsCgJnbQt7elDKFsCun4Rpk84surkb9wis1vEmOuhx1NRp4lSaU8nukhVwi/kOxoIf/7O/N2FoemgG3q0vVzPwB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [project = _t, stage = _t, date = _t, #"type" = _t, value = _t]),
merge_typeStage = Table.AddColumn(Source, "typeStage", each Text.Combine({[type], [stage]}, " "), type text),
groupTable = Table.Group(merge_typeStage, {"project", "typeStage"}, {{"data", each _, type table [project=text, stage=text, date=text, type=text, value=text, typeStage=text]}}),
addMaxValue = Table.AddColumn(groupTable, "maxValue", each Table.Max([data], "value")),
expandMaxValue = Table.ExpandRecordColumn(addMaxValue, "maxValue", {"value"}, {"value"}),
remDataCol = Table.RemoveColumns(expandMaxValue,{"data"}),
valueDataType = Table.TransformColumnTypes(remDataCol,{{"value", Int64.Type}}),
pivotCols = Table.Pivot(valueDataType, List.Distinct(valueDataType[typeStage]), "typeStage", "value", List.Sum),
addEstCloseOut = Table.AddColumn(pivotCols, "Estimated Close out", each if [Actual Close out] = null then [Target Certification] else null, Int64.Type)
in
addEstCloseOut
Which gives me the following output:
May not be EXACTLY as you wanted re: column names etc, but hopefully a strong startng point for adjustment.
Pete
Proud to be a Datanaut!
Thank you Pete,
All issues where related to my data.
Regards,
Angel
Proud to be a Datanaut!
Hi Pete, quick question,
I'm getting following error: "The column 'Project' of the table wasn't found" on step groupTable = Table.Group(merge_typeStage, {"Project", "typeStage"}, {{"data", each _, type table [project=text, stage=text, date=text, type=text, value=text, typeStage=text]}}),
My data source is Dynamics 365 Source = OData.Feed("https://org.api.crm.dynamics.com/api/data/v9.1/", null, [Implementation="2.0"]). Does this source type need some additional considetarions to use the code that you share with me?
Thanks,
Angel
Proud to be a Datanaut!
Hi,
I identified the mistake. Now I'm stuck on step
addMaxValue = Table.AddColumn(groupTable, "maxValue", each Table.Max([data], "value"))
The error says:
Expression.Error: The specified sort criteria is invalid.
Details:
value
Any idea why?
Thanks,
Thanks Pete,
I already did that befor asking you for new considerations based on the source type, but still not working.
Now I'm realizing the first step (merge_typeStage) is dsplaying an error mesage:"Preview.Error: The current preview value is too complex to display."
this is the code I have at this time. So far, I've being working with only two first steps of your code
let
Source = OData.Feed("https://nadb.api.crm.dynamics.com/api/data/v9.1/", null, [Implementation="2.0"]),
nadb_opportunitymetrics_table = Source{[Name="nadb_opportunitymetrics",Signature="table"]}[Data],
#"Changed Type2" = Table.TransformColumnTypes(nadb_opportunitymetrics_table,{{"nadb_metricstage_oppmt", type text}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Changed Type2", "_nadb_opportunity_oppmt_value", "_nadb_opportunity_oppmt_value - Copy"),
#"Renamed Columns" = Table.RenameColumns(#"Duplicated Column",{{"_nadb_opportunity_oppmt_value - Copy", "ProjectID_value"}}),
#"Tipo cambiado" = Table.TransformColumnTypes(#"Renamed Columns",{{"nadb_value_oppmt", type number}, {"nadb_metricdate_oppmt", type date}}),
#"Removed Columns" = Table.RemoveColumns(#"Tipo cambiado",{"importsequencenumber", "_modifiedby_value", "_modifiedonbehalfby_value", "utcconversiontimezonecode", "_owningteam_value", "nadb_unitmeasure_oppmt", "_createdonbehalfby_value", "_owningbusinessunit_value", "versionnumber", "overriddencreatedon", "timezoneruleversionnumber"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"nadb_measuretype_oppmt", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type","180830000","Target",Replacer.ReplaceText,{"nadb_measuretype_oppmt"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","180830001","Actual",Replacer.ReplaceText,{"nadb_measuretype_oppmt"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value1",{{"createdon", type date}}),
//TRansform code //
merge_typeStage = Table.AddColumn(Source, "typeStage", each Text.Combine({[nadb_measuretype_oppmt], [nadb_metricstage_oppmt]}, " "), type text),
groupTable = Table.Group(merge_typeStage, {"_nadb_opportunity_oppmt_value", "typeStage"}, {{"data", each _, type table [project=text, stage=text, date=text, type=text, value=text, typeStage=text]}}),
addMaxValue = Table.AddColumn(groupTable, "maxValue", each Table.Max([data], "value")),
expandMaxValue = Table.ExpandRecordColumn(addMaxValue, "maxValue", {"value"}, {"value"}),
remDataCol = Table.RemoveColumns(expandMaxValue,{"data"}),
valueDataType = Table.TransformColumnTypes(remDataCol,{{"value", Int64.Type}}),
pivotCols = Table.Pivot(valueDataType, List.Distinct(valueDataType[typeStage]), "typeStage", "value", List.Sum),
addEstCloseOut = Table.AddColumn(pivotCols, "Estimated Close out", each if [Actual Close out] = null then [Target Certification] else null, Int64.Type)
in
// #"Changed Type2"
addEstCloseOut
thanks,
Hi Pete,
Thank you very much for your help. I'm new at power bi son i'm now trying to understand where to use my own field names and all that. But as you said, this is a strong stating point. I'll let you know the results!
Thanks again,
Angel
User | Count |
---|---|
41 | |
26 | |
21 | |
16 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
17 | |
15 |