Forum Discussion
Display line graph for two data from one table
I have one Excel sheet from where I am preparing the Dashboard.
I have data like below
| Task Name | Target Date | Alert Date |
| Task 1 | 13/06/2023 | 13/09/2023 |
| Task 2 | 05/05/2023 | 10/08/2023 |
| Task 3 | 10/05/2023 | 10/07/2023 |
Now I need to prepare a line chart month wise which should have two series one for Targetdate Count and another for Alert Date Count. We need to get in which month how many alerts are there and how many targets are there.
e.g. As per above table
May Target - 2, Alert 0
JuneTarget 1, Alert 0
July Target 0, Alert 1
Aug Target 0, Alert 1
Sep Target 0, Alert 1
Oct Target 0, Alert 0
Thanks for the help.
Upniwesh
Hi upniwesh ,
Here's my solution in Power Query.
1.Create a calendar table and extract months.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtY11DVUio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [StartDate = _t]), #"Added Custom" = Table.AddColumn(Source, "EndDate", each Date.From("2023-12-31")), #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom",{{"EndDate", type date}}), #"Changed Type" = Table.TransformColumnTypes(#"Changed Type2",{{"StartDate", type date}}), #"Added Custom1" = Table.AddColumn(#"Changed Type", "Dates", each {Number.From([StartDate])..Number.From([EndDate])}), #"Expanded Dates" = Table.ExpandListColumn(#"Added Custom1", "Dates"), #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Dates",{{"Dates", type date}}), #"Removed Columns1" = Table.RemoveColumns(#"Changed Type1",{"StartDate", "EndDate"}), #"Added Custom4" = Table.AddColumn(#"Removed Columns1", "MonthName", each Date.MonthName([Dates])), #"Removed Columns" = Table.RemoveColumns(#"Added Custom4",{"Dates"}), #"Removed Duplicates" = Table.Distinct(#"Removed Columns") in #"Removed Duplicates"2.Create two month name column and a custom column contains 1 in the main table.
=Date.MonthName([Target Date])=Date.MonthName([Alert Date])3.In Query1, click "Merge Queries".
Merge with the main table as follows.
4.Expand the custom column contains 1.
5.Merge again, replacing the matching column with Alert Month. After the merger is complete, expand the Alert Month column.
6.Group by MonthName and sum two columns.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
Source :
Task Name Target Date Alert Date Task 1 13/06/2023 13/09/2023 Task 2 05/05/2023 10/08/2023 Task 3 10/05/2023 10/07/2023 If someone can help to get data by Power BI Query in the below format from the above table?
Required:
Month Alert Count TargetCount Jan 0 0 Feb 0 0 March 0 0 April 0 0 May 0 2 June 0 1 July 1 0 Aug 1 0 Sep 1 0 Oct 0 0 Nov 0 0 Dec 0 0 --
Thanks
Upniwesh
Hi upniwesh ,
Here's my solution in Power Query.
1.Create a calendar table and extract months.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtY11DVUio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [StartDate = _t]), #"Added Custom" = Table.AddColumn(Source, "EndDate", each Date.From("2023-12-31")), #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom",{{"EndDate", type date}}), #"Changed Type" = Table.TransformColumnTypes(#"Changed Type2",{{"StartDate", type date}}), #"Added Custom1" = Table.AddColumn(#"Changed Type", "Dates", each {Number.From([StartDate])..Number.From([EndDate])}), #"Expanded Dates" = Table.ExpandListColumn(#"Added Custom1", "Dates"), #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Dates",{{"Dates", type date}}), #"Removed Columns1" = Table.RemoveColumns(#"Changed Type1",{"StartDate", "EndDate"}), #"Added Custom4" = Table.AddColumn(#"Removed Columns1", "MonthName", each Date.MonthName([Dates])), #"Removed Columns" = Table.RemoveColumns(#"Added Custom4",{"Dates"}), #"Removed Duplicates" = Table.Distinct(#"Removed Columns") in #"Removed Duplicates"2.Create two month name column and a custom column contains 1 in the main table.
=Date.MonthName([Target Date])=Date.MonthName([Alert Date])3.In Query1, click "Merge Queries".
Merge with the main table as follows.
4.Expand the custom column contains 1.
5.Merge again, replacing the matching column with Alert Month. After the merger is complete, expand the Alert Month column.
6.Group by MonthName and sum two columns.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi
let
Source = YourSource,
UnPivot = Table.UnpivotOtherColumns(Source, {"Task Name"}, "Attribute", "Value"),
StartOfMonth = Table.TransformColumns(UnPivot, {{"Value", each Date.StartOfMonth(_), type date}}),
Pivot = Table.Pivot(StartOfMonth, List.Distinct(StartOfMonth[Attribute]), "Attribute", "Task Name", List.Count),
List_Month = Table.FromColumns({List.Transform({1..12}, each Date.StartOfMonth(#date(2023,_,1)))},type table [Month= date]),
Join = Table.NestedJoin(List_Month, {"Month"}, Pivot, {"Value"}, "Table", JoinKind.LeftOuter),
Expand = Table.ExpandTableColumn(Join, "Table", {"Target Date", "Alert Date"}, {"Target Date", "Alert Date"}),
Sort = Table.Sort(Expand,{{"Month", Order.Ascending}}),
MonthName = Table.TransformColumns(Sort, {{"Month", each Date.MonthName(_), type text}})
in
MonthNameStéphane
Hi,
Another solution without Table.Join
let
Source = YourSource,
UnPivot = Table.UnpivotOtherColumns(Source, {"Task Name"}, "Attribute", "Month"),
StartOfMonth = Table.TransformColumns(UnPivot, {{"Month", each Date.StartOfMonth(_), type date}}),
Pivot = Table.Pivot(StartOfMonth, List.Distinct(StartOfMonth[Attribute]), "Attribute", "Task Name", List.Count),
Combine = Table.Combine({Pivot,Table.FromColumns({List.Transform({1..12}, each Date.StartOfMonth(#date(2023,_,1)))},type table [Month= date])}),
Distinct = Table.Distinct(Combine, {"Month"}),
Sort = Table.Sort(Distinct,{{"Month", Order.Ascending}}),
MonthName = Table.TransformColumns(Sort, {{"Month", each Date.MonthName(_), type text}})
in
MonthName
