Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

Creating new columns from two different tables

  • 1.  Creating new columns from two different tables

    Posted 27 days ago

    Hi,

     

    I've seen some posts on this but haven't been able to figure out what I'm doing wrong. I have two tables that have daily eCommerce sales, orders, and units by product. One table is from shopify, the other is from Amazon. I would like to create a combined/total daily view by product for sales, orders, and units and am having difficulty doing so.

     

    I know I need to create a relationship between the two tables, but I'm not sure what to connect on – date? Product? I already have a connection for both tables to a calendar table, but when I try and connect the two sales tables, it only allows me to create a many to many relationship which I've read doesn't work when creating new columns. I think this is the step that's causing me problems

     

    Once that connection is properly made, I know I then need to use the related function. From what I read, you can create this in one of the existing tables, but wouldn't it be potentially better to create a new table so as not to muddle the data?

     

    Thanks in advance for the help

     

    -Sara

     

     

    Conference-PBI_200x200


  • 2.  RE: Creating new columns from two different tables

    Top Contributor
    Posted 27 days ago
    There are two ways to achieve what you want.

    First, let's think of this in terms of a star schema.
    "Star schema" is a dimensional modeling term, and Power BI is designed to work on this structure.
    In a star, you have a central fact, and it has relationships to some number of dimensions that describe the fact (who, what, where, when, etc).

    In your case, you have two facts, and one "conformed" date dimension.
    Conformed just means that more than one fact table can use it.
    You connected both facts to the dimension which is exactly what you want to do.
    Now you can make measures from both tables, add them together, and slice by date.

    You should not ever connect facts to facts though. One step too far!
    Facts connect to dimensions only. (That is so that we can turn the data set into a rubik's cube!)

    But you probably also want to slice by Product, Store, Location, and other attributes.
    So - to do that, you would need to create new dimensions that contains unique values for Product, Store, Location, etc.
    If both fact tables have a productID column, they can both connect to a Product dimension on the ID.

    This would give you a fully modeled star schema (technically a constellation, since there is more than one fact).

    Now - there is another way to do this, but it will only work if you are using Import method.
    In the query editor, you can transform both tables to have the same number/named columns, then use the APPEND transform to combine the tables into one.
    This is akin to a SQL UNION, and stacks the data sets on top of each other to create one data set that connects to your Calendar table.
    In this situation the data is all in one table, so you can slice on the product column in the fact. Kind of a lazy star - but this will only work if you have a single fact table.

    Let me know if you have any questions.







    ------------------------------
    Audrey Abbey
    SR. BI Developer/Analyst
    LeapFrogBI
    Portland OR
    ------------------------------

    Conference-PBI_200x200


  • 3.  RE: Creating new columns from two different tables

    Top Contributor
    Posted 27 days ago
    Hi !

    As Audrey suggested try creating a Fact which includes mapping column from both Amazon & Shopify.

    Also Add a new column in the transformation to identify source As either [Amazon/Shopify]

    You can append these 2 into single fact, & link your dimension to your Fact.

    Regards,

    ------------------------------
    Hasham Bin Niaz
    Director Data & Analytics
    Karachi, Pakistan
    ------------------------------

    Conference-PBI_200x200


  • 4.  RE: Creating new columns from two different tables

    Top Contributor
    Posted 24 days ago
    Hi @Sara Andrews,

    I agree with @Hasham Bin Niaz on this.

    Add a column (if you do not already have one) to differentiate between which table has Amazon's data and which has Shopify.

    Append both your tables into a single table and ​​then from this table, if you don't have dimension tables, create them by using the DISTINCT() or VALUES() function.
    From what I can see in your question, you should have a Dim_Product table and a Dim_Company table.

    Then you can create relationships between the newly created Dimension tables and the appended fact tables.

    You do not need many to many relationships or use the RELATED() function.

    Convert you data model in a Star Schema as @Audrey Abbey has suggested.

    Hope this helps.

    Thank you,​​

    ------------------------------
    Vishesh Jain
    Owner
    VR Construction
    ------------------------------

    Conference-PBI_200x200


  • 5.  RE: Creating new columns from two different tables

    Posted 24 days ago

    Hi All,

     

    Thank you for your responses. I sort of understand the idea of the star schema, but I still don't think I'm implementing this correctly (well, I know I'm not because it's still not working).

     

    This is my current set up:

     

    Red = product dimension that the tables share

    Blue = date dimension that the products share

    Green = fields that are unique to each table

     

    From what I read in your responses – I (1) needed to create a products dimension table. I'm assuming this part isn't working because I don't have some sort of product ID field – but I don't know why I'd need an ID field in this case because I don't have an ID to work from, just the product names that are the same in both tables. Then, (2) I should be combining my two sales data tables into one table? What about the fields that don't match? Are those just left blank/null where they don't exist.

     

    I'm only a few weeks in to using this tool so apologies if my questions are juvenile!

     

    -Sara

     




    Conference-PBI_200x200


  • 6.  RE: Creating new columns from two different tables

    Top Contributor
    Posted 23 days ago
    Hi !

    You did a good try, you model should look like this for Star Schema;

    Dimensions:
    Date (Which is your OCalendar table)
    Product (Populate this with Distinct Products from both Amazon & Shopify)

    Fact:
    Sales (This would be Append table combining both Amazon & Shopify Sales & having common column from sources, also mark a Source Column to represent Sales source)

    Model:
    Date - Sales ( 1 - Many connection based on Date Key)
    Product - Sales (  1 - Many connection based on Product Key / Product Name keeping in mind you have distinct names)

    If you share your current data or schema we will help you build a layout model.

    Regards,

    ------------------------------
    Hasham Bin Niaz
    Director Data & Analytics
    Karachi, Pakistan
    ------------------------------

    Conference-PBI_200x200


  • 7.  RE: Creating new columns from two different tables

    Top Contributor
    Posted 23 days ago
    Edited by Vishesh Jain 23 days ago
    Hi @Sara Andrews,

    Since both your fact tables i.e the AMZ and Shopify​, do not have the same columns, it will be hard to merge them without doing some ETL process.

    As far as your product table is concerned, you can try creating it by using UNION() and DISTINCT() functions.

    Something like this:
    Table 2 = DISTINCT(
    UNION(DISTINCT('AMZ'[Product]), DISTINCT('Shopify'[Product]))
    )

    This will take distinct values from the product column in both your AMZ and Shopify tables, combine them into 1 and then take distinct values from that combined column, as values might be repeated after combining distinct values from both product columns.

    Product IDs are preferred over names as a product might have similar names, but it cannot have the same ID.
    Also from a performance point of view, numbers compress better than string/text.

    If you could upload a sample file, the community can definitely have a look at it and try to come up with a solution.

    Hope this was helpful.

    Thank you,


    ------------------------------
    Vishesh Jain
    Owner
    VR Construction
    ------------------------------

    Conference-PBI_200x200


  • 8.  RE: Creating new columns from two different tables

    Top Contributor
    Posted 23 days ago
    This looks great!

    You don't really have to combine (append) the tables if you don't want to.
    It is an option. You would need to do some work in the Query Editor.
    If you want to post a file with some sample records in each table, I can work it up for you and show you the steps.
    Basically it is just removing, renaming and reordering the columns, such that both tables have the same number and type of columns, in the same order.

    But you can absolutely have two fact tables with two conformed dimensions (date and product).
    This is called a "constellation" since it has two central facts (stars)

    Since you are new, I would try making it work with what you have so far.
    We can use Dax measures to combine the sales.

    So now you want to create some Measures.
    Make sure these columns are data type decimal.

    1. AMZ Sales = SUM(OrderedProductSales)
    2. Shopify Sales = SUM(Total Sales)
    3. Total Sales = [AMZ Sales] + [Shopify Sales]

    Now, you can put your Total Sales measure in a visual, in the "Values" section.
    Dates or Products can go on rows, columns, category or X axis (depending on what type of visual).

    With this simple structure, you can calculate:
    Amazon sales by product and/or date
    Shopify sales by product and/or date
    Total sales by product and/or date
    Amazon sales as a % of Total sales
    Shopify sales as a % of Total sales

    You can also make measures for the Quantities for Amazon, Shopify and Total.
    With that, you can calculate average prices for each product, over time.

    This is the secret of Power BI.
    You can do a LOT with very little data.




    ------------------------------
    Audrey Abbey
    SR. BI Developer/Analyst
    LeapFrogBI
    Portland OR
    ------------------------------

    Conference-PBI_200x200