Forum Discussion
Multiple text substitution with a Table.NestedJoin
I'm trying to substitute multiple text values with a table that contains substitutes.
On the merging step I got an error:
What's wrong with the query ?
The query is following:
let
tabletojoin=Table.FromRecords({
[ID = 1, Name = "cічень"],
[ID = 2, Name = "лютий"],
[ID = 3, Name = "березень"],
[ID = 4, Name = "квітень"],
[ID = 5, Name = "травень"],
[ID = 6, Name = "червень"],
[ID = 7, Name = "липень"],
[ID = 8, Name = "серпень"],
[ID = 9, Name = "вересень"],
[ID = 10, Name = "жовтень"],
[ID = 11, Name = "листопад"]
}),
Source = Excel.Workbook(File.Contents("C:\Users\a.ivanov\AppData\Local\Packages\Microsoft.MicrosoftPowerBIDesktop_8wekyb3d8bbwe\AC\INetCache\W0A9STZG\Exchange_r[1].xls"), null, true),
#"1" = Source{[Name="2020"]}[Data],
#"Removed Columns" = Table.RemoveColumns(#"1",{"Column14"}),
#"Removed Top Rows" = Table.Skip(#"Removed Columns",3),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
#"Filtered Rows" = Table.SelectRows(#"Promoted Headers", each ([Column1] = "1 Долар США ")),
#"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Column1", "Валюта"}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Renamed Columns", {"Валюта"}, "Attribute", "Value"),
#"Sorted Rows" = Table.Sort(#"Unpivoted Columns",{{"Attribute", Order.Ascending}}),
jointable=Table.NestedJoin(#"Sorted Rows", {"Attribute"}, tabletojoin, {"Name"}, JoinKind.LeftOuter),
#"Expanded tabletojoin1" = Table.ExpandTableColumn(jointable, "tabletojoin", {"Id"}, {"tabletojoin.Id"})
in
#"Expanded tabletojoin1"
You are missing the column name that contains the nested join in your parameter list. It goes just before the join kind.
2 Replies
- artemusMicrosoft Employee
You are missing the column name that contains the nested join in your parameter list. It goes just before the join kind.
- RaaassottoHelper I
jointable=Table.NestedJoin(#"Sorted Rows", {"Attribute"}, tabletojoin, {"Name"},"newcolumnname",JoinKind.LeftOuter),