Forum Discussion

dimi2001's avatar
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's avatar
    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

v-juanli-msft's avatar
v-juanli-msft
Community Support
8 years ago

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's avatar
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's avatar
    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