Join us at FabCon Atlanta from March 16 - 20, 2026, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
Register now!The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now! Learn more
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"
Solved! Go to Solution.
You are missing the column name that contains the nested join in your parameter list. It goes just before the join kind.
You are missing the column name that contains the nested join in your parameter list. It goes just before the join kind.
jointable=Table.NestedJoin(#"Sorted Rows", {"Attribute"}, tabletojoin, {"Name"},"newcolumnname",JoinKind.LeftOuter),
The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now!