Data model design help

[dimi2001]

Hi everyone

I am trying to improve my modelling skills and looking for some feedback on how to turn this excel flat file format into an appropriate datamodel in PowerBI. Attached is an example of what the initial data looks like.

I am thinking there should be a table for Company, Sales, Expenses, Profit, then a Date table?

Company would have an ID and Name, then I can include Company in the other data Tables (Sales, Expenses, Profit) which are linked to the Date table which has the FY (financial year)

Any thoughts on this approach? This is an example of the resulting Sales data table. Or should I just include Expenses and Profit as additional columns in this table too?

I also have the problem of how I pivot the first data view, to the second if anyone has tips for Power Query.

[v-juanli-msft]

Hi @dimi2001

1.Transform->Transpose

2.Transform->Fill->Fill down

3.Transform->Use First Row as Headers

4.Select “companyA” to “companyG”, then click on Transform->Unpivot columns (only selected columns)

5.Add column->Duplicated column (copy of column “Attribute”)

6.Select columns “Column1”, then select Transform->Pivot Column,

7.Add column-> Add conditional column

Use the same condition to add column “Total Expenses1”, “Net Profit1”.

8.finally, rename the columns

"Column2"->"FY"

"Attribute - Copy"->"company"

9.create a new table, New Source->Blank Query

code in advanced editor

let

    Source1 = Table.SelectColumns(Sheet3,{"FY","company","Total sales1"}),

    New1=Table.SelectRows(Source1,each[Total sales1]<>null),

    Query1= Table.AddColumn(New1, "Merged", each Text.Combine({[FY], [company]}, " "), type text),

    Source2 = Table.SelectColumns(Sheet3,{"FY","company","Total Expenses1"}),

    New2=Table.SelectRows(Source2,each[Total Expenses1]<>null),

    Query2= Table.AddColumn(New2, "Merged", each Text.Combine({[FY], [company]}, " "), type text),

    Source3 = Table.SelectColumns(Sheet3,{"FY","company","Net Profit1"}),

    New3=Table.SelectRows(Source3,each[Net Profit1]<>null),

    Query3= Table.AddColumn(New3, "Merged", each Text.Combine({[FY], [company]}, " "), type text),

    Source = Table.NestedJoin(Query1,{"Merged"},Query2,{"Merged"},"Query2",JoinKind.LeftOuter),

    #"Expanded Query2" = Table.ExpandTableColumn(Source, "Query2", {"Total Expenses1"}, {"Query2.Total Expenses1"}),

    #"Merged Queries" = Table.NestedJoin(#"Expanded Query2",{"Merged"},Query3,{"Merged"},"Query3",JoinKind.LeftOuter),

    #"Expanded Query3" = Table.ExpandTableColumn(#"Merged Queries", "Query3", {"Net Profit1"}, {"Query3.Net Profit1"})

in

#"Expanded Query3"

Best Regards

Maggie

[dimi2001]

Hi Maggie, this is great!

Are you able to explain a bit more about adding additional columns of data to the new query?

ie this one shows Total Sales, Expenses, Profit. If there was another column, eg Total Tax

I add

    Source4 = Table.SelectColumns(RawData,{"FY","company","Total Tax1"}),
    New4=Table.SelectRows(Source4,each[Total Tax1]<>null),
    Query4= Table.AddColumn(New4, "Merged", each Text.Combine({[FY], [company]}, " "), type text),

Then how do I expand this out? Just trying to break down the logic in this section of the query:

 Source = Table.NestedJoin(Query1,{"Merged"},Query2,{"Merged"},"Query2",JoinKind.LeftOuter),

    #"Expanded Query2" = Table.ExpandTableColumn(Source, "Query2", {"Total Expenses1"}, {"Query2.Total Expenses1"}),
    #"Merged Queries" = Table.NestedJoin(#"Expanded Query2",{"Merged"},Query3,{"Merged"},"Query3",JoinKind.LeftOuter),
    #"Expanded Query3" = Table.ExpandTableColumn(#"Merged Queries", "Query3", {"Net Profit1"}, {"Query3.Net Profit1"})

in
#"Expanded Query3"

And actually what is the key point of actually doing this join when you have already transformed and pivitoed the columns into an analysable format?

Is it to just clean up the query and reduce the rows in the table?

[v-juanli-msft · Accepted solution]

Hi @dimi2001

"And actually what is the key point of actually doing this join when you have already transformed and pivitoed the columns into an analysable format? 

Is it to just clean up the query and reduce the rows in the table?"

the key point of join is to create a key column which can match between two tables.

Let me explain the following code

let

    Source1 = Table.SelectColumns(Sheet3,{"FY","company","Total sales1"}),

    New1=Table.SelectRows(Source1,each[Total sales1]<>null),

    Query1= Table.AddColumn(New1, "Merged", each Text.Combine({[FY], [company]}, " "), type text),

    Source2 = Table.SelectColumns(Sheet3,{"FY","company","Total Expenses1"}),

    New2=Table.SelectRows(Source2,each[Total Expenses1]<>null),

    Query2= Table.AddColumn(New2, "Merged", each Text.Combine({[FY], [company]}, " "), type text),

    Source3 = Table.SelectColumns(Sheet3,{"FY","company","Net Profit1"}),

    New3=Table.SelectRows(Source3,each[Net Profit1]<>null),

    Query3= Table.AddColumn(New3, "Merged", each Text.Combine({[FY], [company]}, " "), type text),

{add three tables, each table come from the original table(sheet3),
  Then in each table, create a key column "Merged", these columns in the three tables are all same. 
   So you can use this column in each table as a key column to merge tables.}

    Source = Table.NestedJoin(Query1,{"Merged"},Query2,{"Merged"},"Query2",JoinKind.LeftOuter),
{step1.merge Query1 and Query2 based on the "Merged" column.}

    #"Expanded Query2" = Table.ExpandTableColumn(Source, "Query2", {"Total Expenses1"}, {"Query2.Total Expenses1"}),
{step2.after stpe1, expand the new query to add column "Total Expenses1"}

    #"Merged Queries" = Table.NestedJoin(#"Expanded Query2",{"Merged"},Query3,{"Merged"},"Query3",JoinKind.LeftOuter),
{step3}
    #"Expanded Query3" = Table.ExpandTableColumn(#"Merged Queries", "Query3", {"Net Profit1"}, {"Query3.Net Profit1"})
{step4}
in

#"Expanded Query3"

I would break down steps above to explain more clear

step1.

step2.

step3.

step4.

In your scenoria (add Query4), you can modify the orginal query as below

Source = Table.NestedJoin(Query1,{"Merged"},Query2,{"Merged"},"Query2",JoinKind.LeftOuter),

    #"Expanded Query2" = Table.ExpandTableColumn(Source, "Query2", {"Total Expenses1"}, {"Query2.Total Expenses1"}),
    #"Merged Queries" = Table.NestedJoin(#"Expanded Query2",{"Merged"},Query3,{"Merged"},"Query3",JoinKind.LeftOuter),
    #"Expanded Query3" = Table.ExpandTableColumn(#"Merged Queries", "Query3", {"Net Profit1"}, {"Query3.Net Profit1"}),
    #"Merged Queries2" = Table.NestedJoin(#"Expanded Query3",{"Merged"},Query4,{"Merged"},"Query4",JoinKind.LeftOuter), 
    #"Expanded Query4" = Table.ExpandTableColumn(#"Merged Queries2", "Query4", {"Total Tax1"}, {"Query3.Total Tax1"}) 
in
#"Expanded Query4"

Best Regards

Maggie