Milestones forecast/actual from columns to rows

[Anonymous]

Hi!

I received an excel file organized similar to this one (fake dataset):

Index	M1 Forecast	M1 Actual	M2 Forecast	M2 Actual	M1 Success	M2 Success
1	01/01/2010	05/01/2010	03/02/2010	03/02/2010	FALSE	TRUE
2	17/01/2010	17/01/2010	09/02/2010	10/02/2010	TRUE	FALSE
3	22/01/2010	23/01/2010	15/02/2010	15/02/2010	FALSE	TRUE
4	25/01/2010	26/01/2010	26/02/2010	28/02/2010	FALSE	FALSE

Where M is for Milestone (and of course the columns and the rows are really in the hundreds).

I basically compare the "Forecast" and the "Actual" dates to evaluate if the Milestone was completed on time.

I would like to:

1. Have a list of Milestones that I can use as a filter.

2. Automate the creation of the "Success" column, to avoid the copy/paste/edit repetitive task for each milestone.

My idea (pseudocode badly mixing Java and M):

- create an array of array:     arr = {{M1F,M1A}, {M2F,M2A}}

- loop through it:  for(i = 0; i<arr.lenght; i++) { Table.AddColumn(MyTable, Milestones[i], each if arr[i][1]< arr[i][0] then true else false )} 

Where Milestones[i] is the String value name of the Milestone, from the Milestones list.

Any idea how to do this in Power Query or DAX?

Thanks

[Mariusz · Accepted solution]

Hi @Anonymous 

If you looking for this as an outcome.

Then you can use the M script below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY7LDcAwCEN34Rwp4JR+ZkHZf42SS2uqSlye5WcRISZN1Hoe1HSBM4yu+IHZQpCRHVQuoBeZpsUcGQFUxuAZZ9OLua0yP4j9A4+J8zXnDQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Index = _t, #"M1 Forecast" = _t, #"M1 Actual" = _t, #"M2 Forecast" = _t, #"M2 Actual" = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Index"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Milestone", "Attribute.2"}),
    #"Pivoted Column" = Table.Pivot(#"Split Column by Delimiter", List.Distinct(#"Split Column by Delimiter"[Attribute.2]), "Attribute.2", "Value"),
    #"Changed Type" = Table.TransformColumnTypes(#"Pivoted Column",{{"Index", Int64.Type}, {"Forecast", type date}, {"Actual", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Success", each [Forecast]=[Actual], type logical )
in
    #"Added Custom"

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

[Anonymous]

That's great, Thanks Mariusz!