PDA

View Full Version : Return Result from Many to Many relationship



dluhut
2017-05-31, 06:36 PM
I have crosspost @ https://www.excelforum.com/excel-formulas-and-functions/1187485-powerpivot-lookup-based-on-2-columns-conditions.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

Dan Bliss
2017-06-17, 07:38 PM
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

Dan Bliss
2017-06-21, 05:37 AM
I read discussions at the crosspost (https://www.excelforum.com/excel-formulas-and-functions/1187485-powerpivot-lookup-based-on-2-columns-conditions.html) that you can only work in PowerPivot, not Power Query. Sorry, my solution here is only in Power Query.

P3Tom
2017-08-30, 12:51 AM
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 (https://ln.sync.com/dl/f1be13420/fe9tziuh-uerf9g9k-qakuqsiw-cxeujirm) is a download link to an Excel 2016 file that uses Dan Bliss's idea with your data.

Tom
www.powerpivotpro.com (http://www.powerpivotpro.com)