Convert columns into a Matrix

[Thomas_Eu]

Hello together,

I am new to Power BI and need some help with some basics. I seached for a solution, but the problem is difficult to describe.

Problem/Situation: I do have a data source for projects. In this, the milestones (dates) are stored in columns (see grey examples). We have 5 milestones/gates for each project, BUT 3 times. 1x Initially planned, then actuall approved and the actual forecast.

My question is now: How can I show them in a visual in columns (blue example, including calculated column) and how can I generate a chart, which shows the delay (like shown in my example).

If there are diffeernet steps necessay and there is a good basic traing course, how to handle this, feel free, to post a link to the course. If it is more easy, feel free to just explain the steps necessary.

If the topic has to be moved to PowerQuery, please move it.

Best regards,

Thomas

[BekahLoSurdo · Accepted solution]

Hi @Thomas_Eu,

Here is one way to do it (using Power Query). I started with a similiar table but used different dates:

Project

MS1 (Plan)

MS2 (Plan)

MS3 (Plan)

MS4 (Plan)

MS5 (Plan)

MS1 (Appro.)

MS2 (Appro.)

MS3 (Appro.)

MS4 (Appro.)

MS5 (Appro.)

MS1 (Actual)

MS2 (Actual)

MS3 (Actual)

MS4 (Actual)

MS5 (Actual)

Project 01

1/1/2019

1/2/2019

1/3/2019

1/4/2019

1/5/2019

1/6/2019

1/7/2019

1/8/2019

1/9/2019

########

########

########

########

########

########

Project 02

1/2/2019

1/3/2019

1/4/2019

1/5/2019

1/6/2019

1/7/2019

1/8/2019

1/9/2019

########

########

########

########

########

########

########

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKz0pNLlEwMFTSUTLUN9Q3MjC0BDONEExjBNMEwTRFMM0QTHME0wLBtEQwDQ2Q2Ej2GSJZaIhkoyGSlYZQO2N1kFxuNOidC2ZDLY2NBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Project = _t, #"MS1 (Plan)" = _t, #"MS2 (Plan)" = _t, #"MS3 (Plan)" = _t, #"MS4 (Plan)" = _t, #"MS5 (Plan)" = _t, #"MS1 (Appro.)" = _t, #"MS2 (Appro.)" = _t, #"MS3 (Appro.)" = _t, #"MS4 (Appro.)" = _t, #"MS5 (Appro.)" = _t, #"MS1 (Actual)" = _t, #"MS2 (Actual)" = _t, #"MS3 (Actual)" = _t, #"MS4 (Actual)" = _t, #"MS5 (Actual)" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project", type text}, {"MS1 (Plan)", type date}, {"MS2 (Plan)", type date}, {"MS3 (Plan)", type date}, {"MS4 (Plan)", type date}, {"MS5 (Plan)", type date}, {"MS1 (Appro.)", type date}, {"MS2 (Appro.)", type date}, {"MS3 (Appro.)", type date}, {"MS4 (Appro.)", type date}, {"MS5 (Appro.)", type date}, {"MS1 (Actual)", type date}, {"MS2 (Actual)", type date}, {"MS3 (Actual)", type date}, {"MS4 (Actual)", type date}, {"MS5 (Actual)", type date}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Project"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Columns", "Attribute", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
    #"Replaced Value" = Table.ReplaceValue(#"Split Column by Delimiter","MS","Gate ",Replacer.ReplaceText,{"Attribute.1"}),
    #"Added Conditional Column" = Table.AddColumn(#"Replaced Value", "Gate Type", each if [Attribute.2] = "(Plan)" then "Initial Plan" else if [Attribute.2] = "(Appro.)" then "Approved" else if [Attribute.2] = "(Actual)" then "Forecast" else "n/a"),
    #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"Attribute.2"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[#"Gate Type"]), "Gate Type", "Value"),
    #"Added Delay" = Table.AddColumn(#"Pivoted Column", "Delay", each [Forecast]-[Approved])
in
    #"Added Delay"

In short, I unpivoted the data, separated the Gate from the Gate Type and then re-pivoted on just the type. You can then filter your visual by Project as you are only showing one. 

Let me know if you need anything further.

Best,

Bekah

[Thomas_Eu]

Hello BekahLoSurdo,

tryed a bit around. And it works 🙂 Many thanks.

How easy such a solution can be....

Best regards,

Thomas

[BekahLoSurdo]

Hi @Thomas_Eu,

Happy to help! Sorry the original post wasn't very detailed; it's hard to know how much experience people have so I generally start with less information and add more if needed. The code that I provided should be able to be inserted right into:

Power Query Editor > View > Advanced Editor

You will need to update your Source information and table name but this can save you some manual work / deciphering. 

Please don't hesitate to reach out if you run into any issues or if you would like any additional screenshots.

Best,

Bekah

[Thomas_Eu]

Hi BekahLoSurdo,

is it possible, to upload screenshots from the stingle steps (unpivot, split, add, ...), so that I can understand what you are doing.

As said, I am new to Power Bi and still struggling. I have never learned PowerQuery and therefor need some more info to understand.

Best regards, Thomas