Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi, I would like some help with nesting function definition in query. So I have situation like one described here https://social.technet.microsoft.com/wiki/contents/articles/32915.power-bi-merge-query-with-m.aspx and I would like to place this function in M script of query so it looks similar to this.
let
Source = Csv.Document(File.Contents("C:\Temp\one.csv"),[Delimiter=",",Encoding=1252]),
Source1 = Csv.Document(File.Contents("C:\Temp\two.csv"),[Delimiter=",",Encoding=1252]),
/**********************/
let BulkReplace = (DataTable as table, FindReplaceTable as table, DataTableColumn as list) =>
let
//Convert the FindReplaceTable to a list using the Table.ToRows function
//so we can reference the list with an index number
FindReplaceList = Table.ToRows(FindReplaceTable),
//Count number of rows in the FindReplaceTable to determine
//how many iterations are needed
Counter = Table.RowCount(FindReplaceTable),
//Define a function to iterate over our list
//with the Table.ReplaceValue function
BulkReplaceValues = (DataTableTemp, n) =>
let
//Replace values using nth item in FindReplaceList
ReplaceTable = Table.ReplaceValue(
DataTableTemp,
//replace null with empty string in nth item
if FindReplaceList{n}{0} = null then "" else FindReplaceList{n}{0},
if FindReplaceList{n}{1} = null then "" else FindReplaceList{n}{1},
Replacer.ReplaceText,
DataTableColumn
)
in
//if we are not at the end of the FindReplaceList
//then iterate through Table.ReplaceValue again
if n = Counter - 1
then ReplaceTable
else @BulkReplaceValues(ReplaceTable, n + 1),
//Evaluate the sub-function at the first row
Output = BulkReplaceValues(DataTable, 0)
in
Output
in
BulkReplace
/***************************/
#"Promoted Headers" = Table.PromoteHeaders(Source),
#"Promoted Headers1" = Table.PromoteHeaders(Source1),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Column1", type text}, {"Column2", Int64.Type}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers1",{{"Column1", type text}, {"Column2", Int64.Type}})
#"Merged Queries" = Table.NestedJoin(#"Changed Type",{"Column1"},#"Changed Type1",{"Column1"},"NewColumn",JoinKind.LeftOuter),
#"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"Column2"}, {"NewColumn.Column2"})
in
#"Expanded NewColumn"
Thanks for any advice you can provide,
Tom
Hi @tomislav ,
I think that the code you are providing has few issues, maybe this is why it does not work for you.
This is the code as I see it:
let
Source = Csv.Document(File.Contents("C:\Temp\one.csv"),[Delimiter=",",Encoding=1252]),
Source1 = Csv.Document(File.Contents("C:\Temp\two.csv"),[Delimiter=",",Encoding=1252]),
/**********************/
BulkReplace = (DataTable as table, FindReplaceTable as table, DataTableColumn as list) =>
let
//Convert the FindReplaceTable to a list using the Table.ToRows function
//so we can reference the list with an index number
FindReplaceList = Table.ToRows(FindReplaceTable),
//Count number of rows in the FindReplaceTable to determine
//how many iterations are needed
Counter = Table.RowCount(FindReplaceTable),
//Define a function to iterate over our list
//with the Table.ReplaceValue function
BulkReplaceValues = (DataTableTemp, n) =>
let
//Replace values using nth item in FindReplaceList
ReplaceTable = Table.ReplaceValue(
DataTableTemp,
//replace null with empty string in nth item
if FindReplaceList{n}{0} = null then "" else FindReplaceList{n}{0},
if FindReplaceList{n}{1} = null then "" else FindReplaceList{n}{1},
Replacer.ReplaceText,
DataTableColumn
)
in
//if we are not at the end of the FindReplaceList
//then iterate through Table.ReplaceValue again
if n = Counter - 1
then ReplaceTable
else @BulkReplaceValues(ReplaceTable, n + 1),
//Evaluate the sub-function at the first row
Output = BulkReplaceValues(DataTable, 0)
in
Output,
/***************************/
#"Promoted Headers" = Source,
#"Promoted Headers1" = Source1,
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Column1", type text}, {"Column2", Int64.Type}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers1",{{"Column1", type text}, {"Column2", Int64.Type}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type",{"Column1"},#"Changed Type1",{"Column1"},"NewColumn",JoinKind.LeftOuter),
#"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"Column2"}, {"NewColumn.Column2"})
in
#"Expanded NewColumn"
Regards,
JB
Hi tomislav,
I am not clear about your requirement, so if possible could you please inform me in details(such as your sample data and your expected output)?What error did you get when use below M code? Or what expected output did you want to get when use below M code? Then I will help you more correctly.
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.