Forum Discussion
Solved
Power Query Join tables
Hi, I need some assistance with joining 2 tables in Query Editor. What I want is for each row in table 2 to return rows from table 1 where Table1.”Finanskonto Nummer” >= Table2.”Totaling.1” and T...
Okay I got a working solution now... thanks to ImkeF and Greg_Deckler for all their time and help.
@ImkeF - it's strange that your trick doesn't work for you it worked for me? I would really like to accept your last code as a solution, but I haven't tested it, but I think our solutions are very close to each other.
this is my final code:
let // 1 til 1 relation for konti af type "konto" Source1 = Table.SelectColumns( Table.SelectRows( Finanskonto, each [FinanskontoTypeSort] = 0 ), {"FinanskontoKey"} ), Source1AddFinansKontoKeyIncluded = Table.AddColumn(Source1, "Included.FinanskontoKey", each [FinanskontoKey]), // M til M relation for konti af type "sum" og "til-sum" Source2 = Table.SelectColumns( Table.SelectRows( Finanskonto, each List.Contains({2, 4}, [FinanskontoTypeSort]) ), {"FinanskontoKey", "Regnskab", "Finanskonto Nummer", "Totaling"} ), Source2SplitByDelimiter = Table.SplitColumn( Source2, "Totaling", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv) ), Source2UnpivotedColumns = Table.UnpivotOtherColumns( Source2SplitByDelimiter, {"FinanskontoKey", "Regnskab", "Finanskonto Nummer"}, "Attribute", "Totaling" ), Source2SplitByDelimiter2 = Table.SplitColumn( Source2UnpivotedColumns, "Totaling", Splitter.SplitTextByDelimiter("..", QuoteStyle.Csv), {"Totaling.1", "Totaling.2"} ), Source2ReplaceNulls = Table.ReplaceValue(Source2SplitByDelimiter2, null, each _[Totaling.1], Replacer.ReplaceValue,{"Totaling.2"} ), Source2AddedTotaling1Idx = Table.Join( Source2ReplaceNulls, {"Regnskab", "Totaling.1"}, Table.PrefixColumns(Table.SelectColumns( Finanskonto, {"Regnskab", "Finanskonto Nummer", "Index"} ), "Idx1"), {"Idx1.Regnskab", "Idx1.Finanskonto Nummer"} ), Source2AddedTotaling2Idx = Table.Join( Source2AddedTotaling1Idx, {"Regnskab", "Totaling.2"}, Table.PrefixColumns(Table.SelectColumns( Finanskonto, {"Regnskab", "Finanskonto Nummer", "Index"} ), "Idx2"), {"Idx2.Regnskab", "Idx2.Finanskonto Nummer"} ), Source2TotalingList = Table.AddColumn ( Source2AddedTotaling2Idx, "TotalingList", each {[Idx1.Index]..[Idx2.Index]} ), Source2ExpandedTotalList = Table.ExpandListColumn(Source2TotalingList, "TotalingList"), Source2TableJoin = Table.Join( Source2ExpandedTotalList, {"Regnskab", "TotalingList"}, Table.PrefixColumns( Table.SelectColumns(Finanskonto, {"Regnskab", "Index", "FinanskontoKey"} ), "Included"), {"Included.Regnskab", "Included.Index"} ), Source2RemoveOtherColumns = Table.SelectColumns(Source2TableJoin,{"FinanskontoKey", "Included.FinanskontoKey"}), CombineSource1and2 = Table.Combine({Source1AddFinansKontoKeyIncluded, Source2RemoveOtherColumns}) in CombineSource1and2
Good point - as it gave me the idea for a better solution: Instead of a surrogate key, create an index on your (sorted) chart of accounts (on accounts only). This way the increment by 1 will not produce unnecessary numbers where no account matches.
Unfortunately the "trick" with omitting the column names doesn't work - sorry. Instead we can create a dynamic one. You'll find it in steps "NoOfColumns" and "ColumnNames":
let
ChartOfAccounts = #table({"AccountNo", "AccountType"}, {{"K100", "Account"}, {"200", "Account"}, {"K100|200", "Totalling1"}, {"300", "Account"}, {"2000", "Account"},{"3000", "Account"}, {"4000", "Account"}, {"2000..4000", "Totalling2"}, {"5000", "Account"}, {"6000", "Account"}, {"8000", "Account"}, {"9000", "Account"}, {"2000..4000|200|5000..9000", "Totalling3"}}),
#"Filtered Rows" = Table.SelectRows(ChartOfAccounts, each ([AccountType] = "Account")),
Indexed = Table.AddIndexColumn(#"Filtered Rows", "Index", 1, 1),
#"Merged Queries" = Table.NestedJoin(ChartOfAccounts,{"AccountNo"},Indexed,{"AccountNo"},"NewColumn",JoinKind.LeftOuter),
IndexedCoA = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"Index"}, {"Index"}),
#"Filtered Rows1" = Table.SelectRows(IndexedCoA, each (Text.StartsWith([AccountType], "Totalling"))),
NoOfColumns = Table.AddColumn(#"Filtered Rows1", "NumberOfColumns", each List.Count(Text.PositionOf([AccountNo], "|", Occurrence.All))+1),
ColumnNames = List.Transform({1..List.Max(NoOfColumns[NumberOfColumns])}, each "Column"&Text.From(_)),
#"SplitColumn|" = Table.SplitColumn(NoOfColumns,"AccountNo",Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), ColumnNames),
#"Removed Columns" = Table.RemoveColumns(#"SplitColumn|",{"Index", "NumberOfColumns"}),
UpivotColumns = Table.UnpivotOtherColumns(#"Removed Columns", {"AccountType"}, "Attribute", "Value"),
#"Removed Columns1" = Table.RemoveColumns(UpivotColumns,{"Attribute"}),
#"SplitColumn.." = Table.SplitColumn(#"Removed Columns1","Value",Splitter.SplitTextByDelimiter("..", QuoteStyle.Csv),{"Totalling.1", "Totalling.2"}),
#"Added Index" = Table.AddIndexColumn(#"SplitColumn..", "Index", 0, 1),
IndexedAccountType = Table.AddColumn(#"Added Index", "IndexedAccountType", each Text.Combine({[AccountType], Text.From([Index], "de-DE")}, "--"), type text),
#"Removed Columns3" = Table.RemoveColumns(IndexedAccountType,{"AccountType", "Index"}),
AllAccountsInOneColumn = Table.UnpivotOtherColumns(#"Removed Columns3", {"IndexedAccountType"}, "Attribute", "Value"),
LookupAccountIndex = Table.NestedJoin(AllAccountsInOneColumn,{"Value"},IndexedCoA,{"AccountNo"},"NewColumn",JoinKind.LeftOuter),
ShowAccountIndex = Table.ExpandTableColumn(LookupAccountIndex, "NewColumn", {"Index"}, {"Index"}),
#"Removed Columns2" = Table.RemoveColumns(ShowAccountIndex,{"Value"}),
PivotBackTo2Columns = Table.Pivot(#"Removed Columns2", List.Distinct(#"Removed Columns2"[Attribute]), "Attribute", "Index"),
ListOfIndices = Table.AddColumn(PivotBackTo2Columns, "AccountIndices", each if [Totalling.2]=null then {[Totalling.1]} else {[Totalling.1]..[Totalling.2]}),
#"Expanded AccountIndices" = Table.ExpandListColumn(ListOfIndices, "AccountIndices"),
LookupAccountNo = Table.NestedJoin(#"Expanded AccountIndices",{"AccountIndices"},IndexedCoA,{"Index"},"NewColumn",JoinKind.LeftOuter),
ShowAccountNo = Table.ExpandTableColumn(LookupAccountNo, "NewColumn", {"AccountNo"}, {"AccountNo"}),
CleanUpTotalling = Table.SplitColumn(ShowAccountNo,"IndexedAccountType",Splitter.SplitTextByEachDelimiter({"--"}, QuoteStyle.Csv, true),{"IndexedAccountType.1", "IndexedAccountType.2"}),
#"Removed Other Columns" = Table.SelectColumns(CleanUpTotalling,{"IndexedAccountType.1", "AccountNo"}),
#"Sorted Rows" = Table.Sort(#"Removed Other Columns",{{"IndexedAccountType.1", Order.Ascending}})
in
#"Sorted Rows"
This is a full code incl. sample data which you can copy into your advanced editor and follow the steps along. But don't use Internet Explorer, because that's buggy and will insert non-printable characters or like which will cause strange error-messages!
Okay I got a working solution now... thanks to ImkeF and Greg_Deckler for all their time and help.
@ImkeF - it's strange that your trick doesn't work for you it worked for me? I would really like to accept your last code as a solution, but I haven't tested it, but I think our solutions are very close to each other.
this is my final code:
let // 1 til 1 relation for konti af type "konto" Source1 = Table.SelectColumns( Table.SelectRows( Finanskonto, each [FinanskontoTypeSort] = 0 ), {"FinanskontoKey"} ), Source1AddFinansKontoKeyIncluded = Table.AddColumn(Source1, "Included.FinanskontoKey", each [FinanskontoKey]), // M til M relation for konti af type "sum" og "til-sum" Source2 = Table.SelectColumns( Table.SelectRows( Finanskonto, each List.Contains({2, 4}, [FinanskontoTypeSort]) ), {"FinanskontoKey", "Regnskab", "Finanskonto Nummer", "Totaling"} ), Source2SplitByDelimiter = Table.SplitColumn( Source2, "Totaling", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv) ), Source2UnpivotedColumns = Table.UnpivotOtherColumns( Source2SplitByDelimiter, {"FinanskontoKey", "Regnskab", "Finanskonto Nummer"}, "Attribute", "Totaling" ), Source2SplitByDelimiter2 = Table.SplitColumn( Source2UnpivotedColumns, "Totaling", Splitter.SplitTextByDelimiter("..", QuoteStyle.Csv), {"Totaling.1", "Totaling.2"} ), Source2ReplaceNulls = Table.ReplaceValue(Source2SplitByDelimiter2, null, each _[Totaling.1], Replacer.ReplaceValue,{"Totaling.2"} ), Source2AddedTotaling1Idx = Table.Join( Source2ReplaceNulls, {"Regnskab", "Totaling.1"}, Table.PrefixColumns(Table.SelectColumns( Finanskonto, {"Regnskab", "Finanskonto Nummer", "Index"} ), "Idx1"), {"Idx1.Regnskab", "Idx1.Finanskonto Nummer"} ), Source2AddedTotaling2Idx = Table.Join( Source2AddedTotaling1Idx, {"Regnskab", "Totaling.2"}, Table.PrefixColumns(Table.SelectColumns( Finanskonto, {"Regnskab", "Finanskonto Nummer", "Index"} ), "Idx2"), {"Idx2.Regnskab", "Idx2.Finanskonto Nummer"} ), Source2TotalingList = Table.AddColumn ( Source2AddedTotaling2Idx, "TotalingList", each {[Idx1.Index]..[Idx2.Index]} ), Source2ExpandedTotalList = Table.ExpandListColumn(Source2TotalingList, "TotalingList"), Source2TableJoin = Table.Join( Source2ExpandedTotalList, {"Regnskab", "TotalingList"}, Table.PrefixColumns( Table.SelectColumns(Finanskonto, {"Regnskab", "Index", "FinanskontoKey"} ), "Included"), {"Included.Regnskab", "Included.Index"} ), Source2RemoveOtherColumns = Table.SelectColumns(Source2TableJoin,{"FinanskontoKey", "Included.FinanskontoKey"}), CombineSource1and2 = Table.Combine({Source1AddFinansKontoKeyIncluded, Source2RemoveOtherColumns}) in CombineSource1and2Great - that looks neat and clean :-)
Yes, our solutions are quite similar - you managed with one pivoting-less.
But watch out: Omitting the column names will only work if the Totalling with the most |-signs sits in the first row of your table!
Thank you for that last tip, I will have to handle that.