none
Converting 2 fact tables into 1 one. RRS feed

  • Question

  • I have two fact tables I want to convert into one fact table for my data model to achieve star schema model. 

    I want to bring YTD Budget, YTD Variance & Annual Budget Figures from budget fact table-2 to Actual fact tbl-1. The cost centre and cost element (other name account) fields combining together have multiple instances in transaction table and but one line in Budget table.

    Actual Line items Fact tbl-1:

    Cost Element Cost element name Cost Center Actual Amount
    510000 Salaries & Wages 4100000 21,654
    510000 Salaries & Wages 4100000 21,654
    510000 Salaries & Wages 4100000 4,331
    510000 Salaries & Wages 4100000 -7,768
    510000 Salaries & Wages 4100000 2,099
    510013 Salary Higher Duties 4100000 135
    510013 Salary Higher Duties 4100000 27

    Budget  Fact tbl-2

    CostCtr     Account Budget YTD YTD Actual YTD Var Ann Bud
    4100000     510000 445,105 441,030 -4,075 532,194
    4100000     510013 9,480 3,928 -5,552 11,377

    Desired Output

    Cost Element Cost element name Cost Center Actual Amount Budget YTD YTD Var Ann Bud
    510000 Salaries & Wages 4100000 21,654 445,105 -4,075 532,194
    510000 Salaries & Wages 4100000 21,654
    510000 Salaries & Wages 4100000 4,331
    510000 Salaries & Wages 4100000 -7,768
    510000 Salaries & Wages 4100000 2,099
    510013 Salary Higher Duties 4100000 135 9,480 -5,552 11,377



    Sunday, June 2, 2019 11:35 PM

Answers

  • Table.Join or table.NestedJoint with a "gouped index " will do it.
    Source is your tbl-1   and   table-2 is your tbl-2


    let
        Source = Excel.CurrentWorkbook(){[Name="table_1"]}[Content],
        Group = Table.Group(Source, {"Cost Element", "Cost Center"}, {{"content", each _, type table [Cost Element=number, Cost element name=text, Cost Center=number, Actual Amount=number]}}),
        TransformCol = Table.TransformColumns(#"Group", {{"content", each Table.AddIndexColumn(_, "Index", 1, 1)}} ),
        Expand = Table.ExpandTableColumn(#"TransformCol", "content", {"Cost element name", "Index", "Actual Amount"}, {"Cost element name", "Index", "Actual Amount"}),
        Merge = Table.NestedJoin(#"Expand", {"Cost Center", "Cost Element"}, table_2, {"CostCtr", "    Account"}, "table_2", JoinKind.LeftOuter),
        AddGroupedIndex = Table.AddColumn(#"Merge", "table_3", each if [Index] = 1 then [table_2] else null, type table),
        RemCol = Table.RemoveColumns(#"AddGroupedIndex", {"Index", "table_2"}),
        Expand2 = Table.ExpandTableColumn(#"RemCol", "table_3", {"Budget YTD", "YTD Actual", "YTD Var", "Ann Bud"}, {"Budget YTD", "YTD Actual", "YTD Var", "Ann Bud"})
    in Expand2


    • Marked as answer by M.Awal Monday, June 3, 2019 11:56 AM
    • Edited by anthony34 Monday, June 3, 2019 12:25 PM
    Monday, June 3, 2019 6:14 AM
  • Hi. Thanks for looking for into it. Getting and Expression.Error: The name 'table_2' wasn't recognized.  Make sure it's spelled correctly. From step Merge.
    • Marked as answer by M.Awal Monday, June 3, 2019 11:56 AM
    Monday, June 3, 2019 10:51 AM
  • Source is your tbl-1 and  table-2 is your tbl-2
    • Marked as answer by M.Awal Monday, June 3, 2019 11:56 AM
    Monday, June 3, 2019 10:53 AM
  • this is your source :

    Budget  Fact tbl-2

    CostCtr     Account Budget YTD YTD Actual YTD Var Ann Bud
    4100000     510000 445,105 441,030 -4,075 532,194
    4100000     510013 9,480 3,928 -5,552 11,377


    I called it table_2 in my code, simply replace table_2 with the name you are using in the merge step:

    Merge = Table.NestedJoin(#"Expand", {"Cost Center", "Cost Element"}, table_2, {"CostCtr", "    Account"}, "table_2", JoinKind.LeftOuter),


    • Marked as answer by M.Awal Monday, June 3, 2019 11:56 AM
    Monday, June 3, 2019 11:09 AM
  • Hi Anthony: Still getting the error. Can you please have a look at the file below?

    https://1drv.ms/x/s!Amc8fiGpDxekhSs9U6rXDRmfdMiL

    • Marked as answer by M.Awal Monday, June 3, 2019 11:56 AM
    Monday, June 3, 2019 11:25 AM
  • actually, you need to load your second table too:

    • in Excel, select your Sheet2
    • select cell A1 (it could be any cell of your table)
    • click on menu Data
    • click on "From Table/Range

    then done, your second table is loaded, same name as mine (table_2) and you got the result you want in your Query1


    • Edited by anthony34 Monday, June 3, 2019 11:43 AM picture
    • Marked as answer by M.Awal Monday, June 3, 2019 11:56 AM
    Monday, June 3, 2019 11:41 AM

All replies

  • Table.Join or table.NestedJoint with a "gouped index " will do it.
    Source is your tbl-1   and   table-2 is your tbl-2


    let
        Source = Excel.CurrentWorkbook(){[Name="table_1"]}[Content],
        Group = Table.Group(Source, {"Cost Element", "Cost Center"}, {{"content", each _, type table [Cost Element=number, Cost element name=text, Cost Center=number, Actual Amount=number]}}),
        TransformCol = Table.TransformColumns(#"Group", {{"content", each Table.AddIndexColumn(_, "Index", 1, 1)}} ),
        Expand = Table.ExpandTableColumn(#"TransformCol", "content", {"Cost element name", "Index", "Actual Amount"}, {"Cost element name", "Index", "Actual Amount"}),
        Merge = Table.NestedJoin(#"Expand", {"Cost Center", "Cost Element"}, table_2, {"CostCtr", "    Account"}, "table_2", JoinKind.LeftOuter),
        AddGroupedIndex = Table.AddColumn(#"Merge", "table_3", each if [Index] = 1 then [table_2] else null, type table),
        RemCol = Table.RemoveColumns(#"AddGroupedIndex", {"Index", "table_2"}),
        Expand2 = Table.ExpandTableColumn(#"RemCol", "table_3", {"Budget YTD", "YTD Actual", "YTD Var", "Ann Bud"}, {"Budget YTD", "YTD Actual", "YTD Var", "Ann Bud"})
    in Expand2


    • Marked as answer by M.Awal Monday, June 3, 2019 11:56 AM
    • Edited by anthony34 Monday, June 3, 2019 12:25 PM
    Monday, June 3, 2019 6:14 AM
  • Hi. Thanks for looking for into it. Getting and Expression.Error: The name 'table_2' wasn't recognized.  Make sure it's spelled correctly. From step Merge.
    • Marked as answer by M.Awal Monday, June 3, 2019 11:56 AM
    Monday, June 3, 2019 10:51 AM
  • Source is your tbl-1 and  table-2 is your tbl-2
    • Marked as answer by M.Awal Monday, June 3, 2019 11:56 AM
    Monday, June 3, 2019 10:53 AM
  • Sorry I cant see any reference of "tbl-2" in the code above. table_1 is being recognised ok
    Monday, June 3, 2019 11:05 AM
  • this is your source :

    Budget  Fact tbl-2

    CostCtr     Account Budget YTD YTD Actual YTD Var Ann Bud
    4100000     510000 445,105 441,030 -4,075 532,194
    4100000     510013 9,480 3,928 -5,552 11,377


    I called it table_2 in my code, simply replace table_2 with the name you are using in the merge step:

    Merge = Table.NestedJoin(#"Expand", {"Cost Center", "Cost Element"}, table_2, {"CostCtr", "    Account"}, "table_2", JoinKind.LeftOuter),


    • Marked as answer by M.Awal Monday, June 3, 2019 11:56 AM
    Monday, June 3, 2019 11:09 AM
  • Hi Anthony: Still getting the error. Can you please have a look at the file below?

    https://1drv.ms/x/s!Amc8fiGpDxekhSs9U6rXDRmfdMiL

    • Marked as answer by M.Awal Monday, June 3, 2019 11:56 AM
    Monday, June 3, 2019 11:25 AM
  • actually, you need to load your second table too:

    • in Excel, select your Sheet2
    • select cell A1 (it could be any cell of your table)
    • click on menu Data
    • click on "From Table/Range

    then done, your second table is loaded, same name as mine (table_2) and you got the result you want in your Query1


    • Edited by anthony34 Monday, June 3, 2019 11:43 AM picture
    • Marked as answer by M.Awal Monday, June 3, 2019 11:56 AM
    Monday, June 3, 2019 11:41 AM
  • Thanks. Silly on my part.
    Monday, June 3, 2019 11:57 AM
  • do not worry, it is not always obvious ...

    One last comment: the purpose of marking a reply as an answer is to put this answer on top right above the question, so that other users that look at the same question can quickly and easily get to the right answer without reading all the unnecessary intermediary posts.

    On the other hand, if you mark all the posts as an answer, it does not help to find out the right answer.

    I would recommand that you mark as an anwer only the reply that answers your question. Usually there is only one such answer, less often you can get two or more good answers.


    • Edited by anthony34 Monday, June 3, 2019 9:17 PM
    Monday, June 3, 2019 12:24 PM
  • No problem. Cheers
    Monday, June 3, 2019 8:46 PM
  • Hi Anthony: I hit a brick wall with the above solution. In Table.NestedJoin instead of doing a left outer I need to do a full outer join. But when I change it to Full Outer, it doesn’t work. Can you please assist?
    Saturday, June 29, 2019 3:52 AM