Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Trading stock data model question

Hey there, I'm managing a small stock portfolio in Excel. I had a blast doing a lot of analysis using Power Query, but recently i've discovered an issue that made me think the data model i'm using i...
Need Help
Jimmy801's avatar
Jimmy801
Community Champion
5 years ago

Hello Anonymous 

 

you can try to create new table with only the code-column using the column of your transaction-table (or also maybe add the column from your yahoo-query) and get only distinct values. Use this new table as dimension in your datamodel and connect them to both of your facttables (Transaction and yahoo). Maybe some measure will need to be updated but it should work


If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Thank you Jimmy !

Quick question : to be able to add columns to the yahoo query (the dailies table) i'll need to have as many stocks in the yahoo query than the transaction table right ? (in the file example, i have 4 transactions and 3 stocks). In that case, how can i synchronize the list of stock in the transaction table (col B) with the query "list valeurs" (which i'm updating manually everytime i have a new stock to fetch ?

 

Again, thanks.

  • Jimmy801's avatar
    Jimmy801
    Community Champion
    5 years ago

    Hello Anonymous 

     

    we are talking here about one column called "code" right? You can create a new table using such a query, that takes the code-column of both of your queries, combines them and get distinct values.

    Table.Distinct(Table.Combine({Table.SelectColumns(YourYahooQuery, "code"), Table.SelectColumns(YourTAQuery, "code")}), {"code"})

    You can use this table then for connecting your both other queries.


    If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
    Kudoes are nice too

    Have fun

    Jimmy

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Jimmy,

      I understand now what you're suggesting, let me try tonight and get back to you 🙂

      Thanks !!

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Jimmy, i did differently actually. I had to redo almost everything and restart from scratch which allowed to clean a lot of things. 

        What i did was to create an index in both tables and set the relationship on it instead of the stock name (pretty basic when i look back at it).

        Thanks for the help anyway !