Forum Discussion

dimi2001

Regular Visitor

8 years ago

Solved

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 ...

v-juanli-msft

8 years ago

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

dimi2001

Regular Visitor

8 years ago

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

Community Support

8 years ago

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

Forum Discussion

Data model design help

Related content

Data Modeling

Data Modelling Issue

data model design

Data Modelling

DAX measures Clean-up in data model?