cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
commodity_check
Helper II
Helper II

Organising time series data in your data model

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.Microsoftopen.Microsofthigh.Microsoftlow.Microsoftclose.Microsoftdate.Appleopen.Applehigh.Applelow.Appleclose.Appledate.Netflixopen.Netflixhigh.Netflixlow.Netflixclose.Netflix
01/01/2020188.06188.16186.485187.3301/01/202075.0976.1576.0875.1401/01/2020329.51330.84329.42330.75
02/01/2020185.61187.7185.5187.2302/01/202075.1875.575.1775.4502/01/2020331.21332.52330.56330.86
03/01/2020183.25185.41182.65185.3503/01/202075.3175.4175.2175.2303/01/2020331.11332.65332.18332.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.

 

DateopenhighlowcloseCompany
01/01/2020188.06188.16186.485187.33Microsoft
02/01/2020185.61187.7185.5187.23Microsoft
03/01/2020183.25185.41182.65185.35Microsoft

 

dateopenhighlowcloseCompany
01/01/202075.0976.1576.0875.14Apple
02/01/202075.1875.575.1775.45Apple
03/01/202075.3175.4175.2175.23Apple

 

dateopenhighlowcloseCompany
01/01/2020329.51330.84329.42330.75Netflix
02/01/2020331.21332.52330.56330.86Netflix
03/01/2020331.11332.65332.18332.61Netflix

 

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.

DateopenhighlowcloseCompany
01/01/2020188.06188.16186.485187.33Microsoft
02/01/2020185.61187.7185.5187.23Microsoft
03/01/2020183.25185.41182.65185.35Microsoft
01/01/202075.0976.1576.0875.14Apple
02/01/202075.1875.575.1775.45Apple
03/01/202075.3175.4175.2175.23Apple
01/01/2020329.51330.84329.42330.75Netflix
02/01/2020331.21332.52330.56330.86Netflix
03/01/2020331.11332.65332.18332.61Netflix

 

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,

1 ACCEPTED SOLUTION
AlB
Super User III
Super User III

Hi @commodity_check 

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 

SU18_powerbi_badge

 

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

 

View solution in original post

4 REPLIES 4
AlB
Super User III
Super User III

@commodity_check 

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 

 

SU18_powerbi_badge

AlB
Super User III
Super User III

Hi @commodity_check 

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 

SU18_powerbi_badge

 

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

 

View solution in original post

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
Super User IV
Super User IV

@commodity_check , 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.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Urdu Hindi D365 Bootcamp 768x460.png

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors
Top Kudoed Authors