Results 1 to 4 of 4

Thread: Return Result from Many to Many relationship

  1. #1

    Return Result from Many to Many relationship



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have crosspost @ https://www.excelforum.com/excel-for...onditions.html

    I have 2 tables that does not have unique values. Given these, POWERPIVOT does not like it when I try to create a relationship as it's many to many.

    How can I return a value (string as it's description) from the other table without relationship created and it's many to many?

    Transaction Table
    GL Functional Area
    1 Z000
    1 Z001
    1 Z002
    1 Z003
    1 Z003
    1 Z004
    2 Z000
    2 Z001
    2 Z002
    2 Z003
    2 Z003
    2 Z004



    Lookup Table
    Group GL Functional Area
    Grocery Expense 1 Z000, Z001
    Restaurant Expense 1 Z002
    Entertainment Expense 1 Z003
    Other Expense 1 Z004
    Mortgage Expense 2 Z000, Z001
    Utilities Expense 2 Z002
    Interest Expense 2 Z003
    Amortization/Depreciation 2 Z004


    Desired Result in Transaction Table
    GL Functional Area Group
    1 Z000 Grocery Expense
    1 Z001 Grocery Expense
    1 Z002 Restaurant Expense
    1 Z003 Entertainment Expense
    1 Z003 Entertainment Expense
    1 Z004 Other Expense
    2 Z000 Mortgage Expense
    2 Z001 Mortgage Expense
    2 Z002 Utilities Expense
    2 Z003 Interest Expense
    2 Z003 Interest Expense
    2 Z004 Amortization/Depreciation

  2. #2
    Acolyte Dan Bliss's Avatar
    Join Date
    Dec 2016
    Posts
    45
    Articles
    0
    Excel Version
    Office 365
    If you concatenate columns GL & Functional Area values in your Lookup table, you can do an left outer join and get the result you want.


    Step 1: To build queries, I do as Ken Puls recommends, and create separate queries for extract & transform. So I have 2 queries designed to be referenced by all later queries that do all the work. This extra step makes connections for reliable. So the "Transaction" Query code looks like this:
    let
    Source = Excel.CurrentWorkbook(){[Name="Lookup"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Group", type text}, {"GL", Int64.Type}, {"Functional Area", type text}})
    in
    #"Changed Type"


    Follow similarly for "Lookup". If you have trouble doing this, write back and I'll show you the detail.




    Step 2&3: Create new columns in each table which concatenate GL & Functional Area


    1. Make duplicate columns of GL & Functional Area
    2. Merge the copy columns to make a new column, I call "Concat"


    M Code for Step 2:
    let


    // Loading a reference to the Transaction Query
    Source = Transaction,


    // Making a copy of GL & Functional Area
    #"Duplicated Column" = Table.DuplicateColumn(Source, "GL", "GL - Copy"),
    #"Duplicated Column1" = Table.DuplicateColumn(#"Duplicated Column", "Functional Area", "Functional Area - Copy"),


    // Merging the copies of GL & Functional Area into a new Column I call "Concat"
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Duplicated Column1", {{"GL - Copy", type text}}, "en-US"),{"GL - Copy", "Functional Area - Copy"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Concat")
    in
    #"Merged Columns"


    Step 3: Do the same with the Lookup Query. Before we concatenate we must normalize the data. Functional Area must be atomized (only one Z-code in a field), and then unpivot.


    1. Split Functional Area by delimiter ", " separating Z-codes,
    2. Unpivot resulting Functional Area columns
    3. Make duplicate columns of GL & Functional Area
    4. Merge the copy columns to make a new column, I call "Concat"


    M Code for Step 3:


    let
    Source = Lookup,
    #"Split Column by Delimiter" = Table.SplitColumn(Source,"Functional Area",Splitter.SplitTextByDelimiter(", ", QuoteStyle.Csv),{"Functional Area.1", "Functional Area.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Functional Area.1", type text}, {"Functional Area.2", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Group", "GL"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Removed Columns", "GL", "GL - Copy"),
    #"Duplicated Column1" = Table.DuplicateColumn(#"Duplicated Column", "Value", "Functional Area"),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Duplicated Column1", {{"GL - Copy", type text}}, "en-US"),{"GL - Copy", "Value"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Concat"),
    #"Reordered Columns" = Table.ReorderColumns(#"Merged Columns",{"Group", "GL", "Functional Area", "Concat"})
    in
    #"Reordered Columns"


    Step 4: Merge using Left Outer Join on new column, "Concat".


    let
    Source = TransactionConcat,
    #"Merged Queries" = Table.NestedJoin(Source,{"Concat"},LookupConcat,{"Concat"},"NewColumn",JoinKind.LeftOuter),
    #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"Group"}, {"Group"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded NewColumn",{"Concat"})
    in
    #"Removed Columns"


    I think this will give you the right result.
    Dan

  3. #3
    Acolyte Dan Bliss's Avatar
    Join Date
    Dec 2016
    Posts
    45
    Articles
    0
    Excel Version
    Office 365
    I read discussions at the crosspost that you can only work in PowerPivot, not Power Query. Sorry, my solution here is only in Power Query.

  4. #4

    Response

    I have also read your cross-referenced post and your concerns about Power Query are definitely misplaced. Power Query (aka, Get & Transform) is fully able to unpivot many columns.

    The issue here is not many-to-many, the issue is re-shaping a quality tool like Power Pivot to fit unprepared/dirty data instead of re-shaping unprepared/dirty data to fit Power Pivot.

    Here is a download link to an Excel 2016 file that uses Dan Bliss's idea with your data.

    Tom
    www.powerpivotpro.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •