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
BTI_Afuentes
Helper I
Helper I

New summarized table with custom colums

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

ProjectStagedateTypevalue
Project1Certification01/01/2015Target1000
Project1Operation01/10/2018Actual800
Project1Close out01/10/2019Actual900
Project1Post close out01/01/2020Actual1100
Project1Post close out01/01/2021Actual1000

 

The new table would be something like:

ProjectTargetActual Operation (max value)Actual Close outActual Post close out (max value)
Project110008009001100

 

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:

ProjectStagedateTypevalue
Project1Certification01/01/2015Target1000

 

New table will be:

ProjectTargetEstimated Close out
Project110001000

 

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,

 

1 ACCEPTED SOLUTION
BA_Pete
Super User
Super User

Hi @BTI_Afuentes 

 

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:

BTI_Afuentes.PNG

 

May not be EXACTLY as you wanted re: column names etc, but hopefully a strong startng point for adjustment.

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

8 REPLIES 8
BA_Pete
Super User
Super User

Hi @BTI_Afuentes 

 

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:

BTI_Afuentes.PNG

 

May not be EXACTLY as you wanted re: column names etc, but hopefully a strong startng point for adjustment.

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Thank you Pete,

 

All issues where related to my data.

 

Regards,

Angel

Cool, good job!
Thanks for marking as solution.

Pete


Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

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

No, I don't think so. More likely that the column called "Project" in your sample data is called something else in your real source so the code needs to be updated to reflect this.
In the line of code you highlighted, you can see the word "Project", change this to the real column name of where the project name is held, keep the speech marks.


Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

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." 

Preview error.png

 

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

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.

Top Solution Authors