Organising time series data in your data model

[Anonymous]

Hi all,

I recently started using power BI and face some issues where I have to compare a lot of time series data. In order to make it easy for everyone to read I use stock market data of Microsoft, Apple and Netflix in the example below, which is similar to the problem I face. I also will use only data from 1-1-2020 to 3-1-2020 to keep the tables small.

I have the following historical daily data of approximately 100 companies:

• open price
• high price (highest price of that day)
• low price (lowest price of the day)
• close price

I want to be able to compare the companies based on these prices. For example I want to compare the open price of Company A with the open price of Company B etc. 

The easist thing for me would be to download all the data and import it. This way, I will get one big table with Unique columns. However, it is hard for me to easily create relationships this way.

Date.Microsoft	open.Microsoft	high.Microsoft	low.Microsoft	close.Microsoft	date.Apple	open.Apple	high.Apple	low.Apple	close.Apple	date.Netflix	open.Netflix	high.Netflix	low.Netflix	close.Netflix
01/01/2020	188.06	188.16	186.485	187.33	01/01/2020	75.09	76.15	76.08	75.14	01/01/2020	329.51	330.84	329.42	330.75
02/01/2020	185.61	187.7	185.5	187.23	02/01/2020	75.18	75.5	75.17	75.45	02/01/2020	331.21	332.52	330.56	330.86
03/01/2020	183.25	185.41	182.65	185.35	03/01/2020	75.31	75.41	75.21	75.23	03/01/2020	331.11	332.65	332.18	332.61

So a solution I came up with was to create seperate tables and add a "company" column, which serves as the foreign keys. But the problem is that I have 100 of these stocks and this will take a lot of time.

Date	open	high	low	close	Company
01/01/2020	188.06	188.16	186.485	187.33	Microsoft
02/01/2020	185.61	187.7	185.5	187.23	Microsoft
03/01/2020	183.25	185.41	182.65	185.35	Microsoft

date	open	high	low	close	Company
01/01/2020	75.09	76.15	76.08	75.14	Apple
02/01/2020	75.18	75.5	75.17	75.45	Apple
03/01/2020	75.31	75.41	75.21	75.23	Apple

date	open	high	low	close	Company
01/01/2020	329.51	330.84	329.42	330.75	Netflix
02/01/2020	331.21	332.52	330.56	330.86	Netflix
03/01/2020	331.11	332.65	332.18	332.61	Netflix

I also tried the following, to append all the data and keep the "company" column as foreign key. But now the "date column" does not line up anymore.

Date	open	high	low	close	Company
01/01/2020	188.06	188.16	186.485	187.33	Microsoft
02/01/2020	185.61	187.7	185.5	187.23	Microsoft
03/01/2020	183.25	185.41	182.65	185.35	Microsoft
01/01/2020	75.09	76.15	76.08	75.14	Apple
02/01/2020	75.18	75.5	75.17	75.45	Apple
03/01/2020	75.31	75.41	75.21	75.23	Apple
01/01/2020	329.51	330.84	329.42	330.75	Netflix
02/01/2020	331.21	332.52	330.56	330.86	Netflix
03/01/2020	331.11	332.65	332.18	332.61	Netflix

So how can I organise the data of these 100 stocks in order to easily compare the stocks (and eventually visualize these comparisons)??

Kind regards,

[AlB]

@Anonymous 

Sure, you can do it in the query editor. If you already have it in the format you show above, you've already done the most complicated part. This last step is very easy. Just select the 4 price columns and choose "Unpivot columns". Then change column names if necessary. That is it.

Place the following M code in a blank query to see the steps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZDRCoMwDEX/xWfJmqRp6+M+YPsB8WkoCIKy+bDPn7UpWB0U7u1tTpq2bSuDt22RIVPVFYYAxqnBZBzYILvzwLyZx/h6z595WKuu3ngqeAGHWuw1yDD9gbmAGUgUsqkLgcsJyxU/zu4FTBPVAUpSE1KOdtP7skz9deR4rGWiW5/USklxSTFqmSpl5RI7zsjUgMQ6ZgPBamJJEx9vfPbrMI3f66TMmC5hJpDMiMvt3BnmE4wZ3v80mv3le4JHuPsB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, open = _t, high = _t, low = _t, close = _t, Company = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"open", type number}, {"high", type number}, {"low", type number}, {"close", type number}, {"Company", type text}}),

    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Date", "Company"}, "Price type", "Value")
in
    #"Unpivoted Columns"

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[AlB · Accepted solution]

Hi @Anonymous 

The structure of your last table looks good. Do you already have it like that?I haven't understood what the problem you mention is.

You could unpivot further to make it more even more manageable (see below). You'd then be good to go

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

Date	Company	Price type	Value
01/01/2020	Microsoft	open	188,06
01/01/2020	Microsoft	high	188,16
01/01/2020	Microsoft	low	186,485
01/01/2020	Microsoft	close	187,33
02/01/2020	Microsoft	open	185,61
02/01/2020	Microsoft	high	187,7
02/01/2020	Microsoft	low	185,5
02/01/2020	Microsoft	close	187,23
03/01/2020	Microsoft	open	183,25
03/01/2020	Microsoft	high	185,41
03/01/2020	Microsoft	low	182,65
03/01/2020	Microsoft	close	185,35
01/01/2020	Apple	open	75,09
01/01/2020	Apple	high	76,15
01/01/2020	Apple	low	76,08
01/01/2020	Apple	close	75,14
02/01/2020	Apple	open	75,18
02/01/2020	Apple	high	75,5
02/01/2020	Apple	low	75,17
02/01/2020	Apple	close	75,45
03/01/2020	Apple	open	75,31
03/01/2020	Apple	high	75,41
03/01/2020	Apple	low	75,21
03/01/2020	Apple	close	75,23
01/01/2020	Netflix	open	329,51
01/01/2020	Netflix	high	330,84
01/01/2020	Netflix	low	329,42
01/01/2020	Netflix	close	330,75
02/01/2020	Netflix	open	331,21
02/01/2020	Netflix	high	332,52
02/01/2020	Netflix	low	330,56
02/01/2020	Netflix	close	330,86
03/01/2020	Netflix	open	331,11
03/01/2020	Netflix	high	332,65
03/01/2020	Netflix	low	332,18
03/01/2020	Netflix	close	332,61

[Anonymous]

Hello @AlB 

Thanks a lot for your reply. I think This will work, since I can compare the "High" of one stock with the "High" of another stock. Same for the other "Price types".

This unpivoting, I can do this in the query editor? 

I will try it and if it works mark your post as solution. Thanks again! 

[amitchandak]

@Anonymous , It depend on visual, Based on what you want two compare. If you need two slicers, you might need two company dimension table (one is independent ) and select 1 company in each slicer and use.

Date example, how to use two slicer : https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

On line/bar visual you can use company as legend , no need to two slicers.