Return Result from Many to Many relationship

dluhut

New member
Joined
Oct 11, 2011
Messages
6
Reaction score
0
Points
0
I have crosspost @ https://www.excelforum.com/excel-fo...vot-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
 
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
 
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.
 
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
 
Back
Top