Plot line graph where latest time point is always the last

[AVISCCS]

I hope to create some sparklines by some sort of time point. This time point always has the last date at the very right of the graph, regardless the frequency of the date.

I expect the sparklines look like this: 

These are the sparklines for 5 groups.

I created these sparklines in Excel, using a Time point index, which I create manually. Each type may have different frequency.

Type	Date	Frequency	Time point index	Value
A	01/02/2023	Daily	1	44.08566
A	02/02/2023	Daily	2	45.62956
A	03/02/2023	Daily	3	58.95195
A	04/02/2023	Daily	4	91.59686
A	05/02/2023	Daily	5	42.81718
B	07/02/2023	Daily	2	67.58168
B	08/02/2023	Daily	3	38.38583
B	09/02/2023	Daily	4	26.80391
B	10/02/2023	Daily	5	0.964799
C	01/02/2023	Monthly	2	63.14624
C	01/03/2023	Monthly	3	47.25796
C	01/04/2023	Monthly	4	71.7982
C	01/05/2023	Monthly	5	42.97753
D	01/03/2023	Monthly	4	42.42771
D	01/04/2023	Monthly	5	40.48352
E	01/01/2022	Quarterly	2	64.25763
E	01/04/2022	Quarterly	3	43.45508
E	01/07/2022	Quarterly	4	22.45489
E	01/10/2022	Quarterly	5	10.29339

How do I create this sparkline in Power BI and power query? I have long data. I can't create this Time point index manually.

Can any one help?

[Anonymous]

Hi @AVISCCS ,

Agree with pbiuseruk.

Sparklines should be on by default. If you don't see the option to add them, here's how to turn on the feature.

In Power BI Desktop, go to File > Options and settings > Options > Preview features, and select Sparklines.

Create sparklines in a table or matrix in a report (preview) - Power BI | Microsoft Learn

In addition, this sparklines feature is not available in Power Query at the moment.

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.

[pbiuseruk]

Hi,

For sparklines you can make them in Power BI by firstly making a table or a matrix visual and then having a numerical column in there (the one you want to view with the Sparkline).

Then you click on the dropdown of the numerical column from the Table Fields pane and then click Add a Sparkline (as in the screenshot)

Then you'll be prompted to provide an x axis which would be your a date column ideally.

 
Not sure what you meant by the long data but let me know if that helps.

[AVISCCS]

Hi

Thank you for your reply.

I know how to make sparklines in Power BI. The problem is that I cannot create sparklines with the specific feature that I mentioned in my post.

So far I can only think of creating a 'Time Point Index' , then plot it in the sparklines.  The ones in the table was done manually in Excel. If Power BI does not offer this type of sparklines, I will need to create this Index efficiently, not manually. Any idea how to create this Index?

Long data = dataset with a lot of rows.

[pbiuseruk]

Hi,

Could you explain the calculation for how you assign the Time Point Index you're referring to.

If its based on the type, date and some sort of frequency, then you can group things in Power Query by type and date (using group by) and then use a rank function to assign them a value based on something like a count of the frequency. 

[AVISCCS]

The Time Point Index groups by Type, and rank by Date. The latest date gets the highest number of Index.

In my example, the highest number of values among all types is the number assigned to the latest date. 

Type A has the largest number of values (5 values). Type A's latest date is 05/02/2023 and it gets '5' as the index. The day before is 04/02/2023 and it gets '4'. The older the date, the smaller the index.

Type B has only 4 values. Its latest date still gets '5', because that is the highest number of values among all types.

Type D has only 2 values. Its latest date is still '5'.

I have used Power Query to create a grouped-rank, but I only did it to go upward. I've never used it to go downward. Is it possible?

In fact, the highest number can be a constant value of 100, because all the types have under 100 time points. This may help. 

[pbiuseruk · Accepted solution]

I understand it better now, thanks for that. Here's the solution.

You need to make 2 queries to do this.
Query 1 will be to get the max number of occurences and Query 2 will do the indexing and will start from the maximum (retreived from Query 1) and then it will increment by -1.

Query 1 is like this (name it like this "Getting_Max_Number" - don't include quotes):

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jdE9a8MwEAbg/+I5XHRfuruxadqtQ+eQIUOhhdJCSIf++0q2qe1ahg5CEjyIV++dTt1dt+sS7hPtKRGXy/Hy9v5ddixLBJJrzt15N0pqSKpSIVPoTHJD1rM6hGLoJKUhpaxA0Mg+e1MbUqsmcDT0Xh6qtI2c2UAd80z6Rk52YFfnScZGTsrgiQN/JaaNnAkii0X08v5v80+fH7fXKSkDSiZZWF7behMDUou8sLK2Na0hWDgtqK7pWGuY6VDBcTuCDFbIDBe2EaF/N4E465DhYbRYbf3489flenu5TkVI/VzmhZaW7qtgENXkC20t3Y+upFbxmOsyvYbWfq5AwTzox//r8w8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Type = _t, Date = _t, Frequency = _t, #"Time point index" = _t, Value = _t]),
#"Grouped Rows" = Table.Group(Source, {"Type"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
#"Calculated Maximum" = List.Max(#"Grouped Rows"[Count])
in
#"Calculated Maximum"

Query 2 is like this (This is the output of the whole table with the index):

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jdE9a8MwEAbg/+I5XHRfuruxadqtQ+eQIUOhhdJCSIf++0q2qe1ahg5CEjyIV++dTt1dt+sS7hPtKRGXy/Hy9v5ddixLBJJrzt15N0pqSKpSIVPoTHJD1rM6hGLoJKUhpaxA0Mg+e1MbUqsmcDT0Xh6qtI2c2UAd80z6Rk52YFfnScZGTsrgiQN/JaaNnAkii0X08v5v80+fH7fXKSkDSiZZWF7behMDUou8sLK2Na0hWDgtqK7pWGuY6VDBcTuCDFbIDBe2EaF/N4E465DhYbRYbf3489flenu5TkVI/VzmhZaW7qtgENXkC20t3Y+upFbxmOsyvYbWfq5AwTzox//r8w8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Type = _t, Date = _t, Frequency = _t, #"Time point index" = _t, Value = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Type", type text}, {"Date", type date}, {"Frequency", type text}, {"Time point index", Int64.Type}, {"Value", type number}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Date", Order.Descending}}),
#"Grouped Rows" = Table.Group(#"Sorted Rows", {"Type"}, {{"Count", each _, type table [Type=nullable text, Date=nullable date, Frequency=nullable text, Time point index=nullable number, Value=nullable number]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count], "CountInd",Getting_Max_Number,-1)),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Type", "Count"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Type", "Date", "Frequency", "Time point index", "Value", "CountInd"}, {"Type", "Date", "Frequency", "Time point index", "Value", "CountInd"})
in
#"Expanded Custom"

This is how it looks like on my side (CountInd is the new index column):

If this resolved the issue, please mark it as the solution.

[AVISCCS]

It seems to be a great solution.

I can see your codes are similar to those I see in the Advanced Editor, but I have never set up more than 1 queries before. Is there any web page/video I can look at to learn how to put your 2 queries into the Power Query?

[pbiuseruk]

Hope it works for you and I'll show you how to do it below.
If you open up a blank Power BI file - Just open up PBI desktop and select "Blank Report" on the top left.

Then click on the "Get data" drop down and select Blank query:

Then you'll come to this screen below and from there, select Advanced Editor. Select all the code there and delete it. Then copy and paste the code that I put in for my Query 1 and then click Done.

Advanced Editor View:

Once you've done this, then change the name of Query 1 in the Name field (highlighted in screenshot)

Then on the left hand pane, right click and then select, new query and then Blank Query:

Then do the same thing in the advanced editor code for this query (but use the Query 2 code that's in the above comment).

Once you click done, you should see exactly the same output that I showed in the previous comments screenshot - you can then click on Close and Apply on the top left and that will load all the transformations, so that you can begin using the data.

If you get the expected result, could you mark the previous comment as the correct solution because that's the one where the logic is and people would benefit more from that one.