How I can transform this table to another in Power Query

[Anonymous]

Hello All,

Please help me to transform table #1 to table #2. I need to count Open and Closed task in each month separetely by "Originated Date" column, but in the meantime show how many tasks are closed in each month by "Closed Date" column. How I will be able to do so?

Table #1

Table #2

[Joe_Barry · Accepted solution]

Hi @Anonymous 

• Create a date Table with Dax. Date Table Instructions 
• If you have a real date column in your table, then igonore this step, otherwise In Power Query you will need to create a column to create a date from the Originated Date and Closed Date. Presuming the data is showing 2024 add this in the query when creating the column 

if [Originated Date] = "January" then "01/01/2024" else 
if [Originated Date] = "February" then "01/02/2024" else 
if [Originated Date] = "March" then "01/03/2024" else 
if [Originated Date] = "April" then "01/04/2024" else 
if [Originated Date] = "May" then "01/05/2024" else 
if [Originated Date] = "June" then "01/06/2024" else null​

• Repeat for Closed Date and convert both new columns to Date and rename
• Load the the table
• Create a New Table With onec olumn with Status'  Raised, Closed & Open 
• Create an Active  one to Many relationship between the Date coumn in the Date table and the New Originated Date Column
• Repeat this for the Closed column, this won't be an active relationship
• You will need to create some measures

Tasks = SUM(Tasks[Count])​

Open Tasks =
CALCULATE([Tasks], KEEPFILTERS(Tasks[Status] = "Open"))

Closed Tasks =
CALCULATE([Tasks], 
KEEPFILTERS( Tasks[Status] = "Closed"),
USERELATIONSHIP(Date[Date], Tasks[Closed Date]))

•  Create a new measure 

TasksAll = SWITCH (
    SELECTEDVALUE ('Table'[Column1]),
    "Raised", [Tasks],
    "Closed", [Closed Tasks],
    "Open", [Open Tasks])​

Add the column from the Status table to the Row section of a Matrix visual. The Month from the Date table goes into the columsn and the measure above into the Values.

Copy and paste the visual and add the Priority Filter in the filetr pane of teh visual and filter to show the Priority you need to show

I hope this helps

Joe

If you found my answer helpful and it solved your issue, please accept as solution

[dufoq3 · Accepted solution]

Hi @Anonymous,

• for future requests provide sample data as table so we can copy/paste
• your expected result doesn't correspond with sample data so I'm not sure if this is what you want and also I don't know how to calculate Raised

Result

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjBW0lHySswrTSyqBLL8C1LzgBQQmRoqxeoA5Y1Q5J1z8otTU4AMt9SkIqgYfoW+iUXJGQhVhkSpMsLhKGOItDF+Q/CrQnK5MTY3IWwzQpiDpAkhb2iC0I9VgRnCN1jljbHqx/APdnfgC2GYEIZTjLFKmiPciSkJdGQsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Priority = _t, #"Originated Date" = _t, Status = _t, #"Closed Date" = _t, Count = _t]),
    ReplacedValue = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue, Table.ColumnNames(Source)),
    ChangedType = Table.TransformColumnTypes(ReplacedValue,{{"Count", Int64.Type}}),
    GroupedRowsTotal = Table.Group(ChangedType, {"Originated Date", "Status"}, {{"Count", each List.Sum([Count]), type nullable number}}),
    Ad_Priority = Table.AddColumn(GroupedRowsTotal, "Priority", each "Total", type text),
    GroupedRowsMonths = Table.Group(ChangedType, {"Originated Date", "Priority"}, {{"All", each Table.Group(_, {"Status"}, {{"Count", each List.Sum([Count]), type nullable number}}) , type table}}),
    ExpandedAll = Table.ExpandTableColumn(GroupedRowsMonths, "All", {"Status", "Count"}, {"Status", "Count"}),
    SortedRows = Table.Sort(ExpandedAll,{{"Originated Date", Order.Ascending}}),
    CombinedTotalAndMonths = Table.Combine({Ad_Priority, SortedRows}),
    PivotedColumn = Table.Pivot(CombinedTotalAndMonths, List.Distinct(CombinedTotalAndMonths[#"Originated Date"]), "Originated Date", "Count"),
    SortedRows2 = Table.Sort(PivotedColumn,{{"Priority", Order.Ascending}})
in
    SortedRows2

[Joe_Barry · Accepted solution]

Hi @Anonymous 

• Create a date Table with Dax. Date Table Instructions 
• If you have a real date column in your table, then igonore this step, otherwise In Power Query you will need to create a column to create a date from the Originated Date and Closed Date. Presuming the data is showing 2024 add this in the query when creating the column 

if [Originated Date] = "January" then "01/01/2024" else 
if [Originated Date] = "February" then "01/02/2024" else 
if [Originated Date] = "March" then "01/03/2024" else 
if [Originated Date] = "April" then "01/04/2024" else 
if [Originated Date] = "May" then "01/05/2024" else 
if [Originated Date] = "June" then "01/06/2024" else null​

• Repeat for Closed Date and convert both new columns to Date and rename
• Load the the table
• Create a New Table With onec olumn with Status'  Raised, Closed & Open 
• Create an Active  one to Many relationship between the Date coumn in the Date table and the New Originated Date Column
• Repeat this for the Closed column, this won't be an active relationship
• You will need to create some measures

Tasks = SUM(Tasks[Count])​

Open Tasks =
CALCULATE([Tasks], KEEPFILTERS(Tasks[Status] = "Open"))

Closed Tasks =
CALCULATE([Tasks], 
KEEPFILTERS( Tasks[Status] = "Closed"),
USERELATIONSHIP(Date[Date], Tasks[Closed Date]))

•  Create a new measure 

TasksAll = SWITCH (
    SELECTEDVALUE ('Table'[Column1]),
    "Raised", [Tasks],
    "Closed", [Closed Tasks],
    "Open", [Open Tasks])​

Add the column from the Status table to the Row section of a Matrix visual. The Month from the Date table goes into the columsn and the measure above into the Values.

Copy and paste the visual and add the Priority Filter in the filetr pane of teh visual and filter to show the Priority you need to show

I hope this helps

Joe

If you found my answer helpful and it solved your issue, please accept as solution