cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dimi2001 Frequent Visitor
Frequent Visitor

Data model design help

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.

 

Screenshot_1.jpg

 

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?

 

Screenshot_2.jpg


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

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Data model design help

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.

1.png

step2.

2.png

step3.

3.png

step4.

4.png

 

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

View solution in original post

3 REPLIES 3
Community Support Team
Community Support Team

Re: Data model design help

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,

 

4.png

 

7.Add column-> Add conditional column

 

5.png

 

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

 

8.finally, rename the columns

"Column2"->"FY"

"Attribute - Copy"->"company"

 

6.png

 

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"

7.png

 

 

 

Best Regards

Maggie

 

dimi2001 Frequent Visitor
Frequent Visitor

Re: Data model design help

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?

 

 

Community Support Team
Community Support Team

Re: Data model design help

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.

1.png

step2.

2.png

step3.

3.png

step4.

4.png

 

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

View solution in original post

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (1,905)