Hi I have the following code that - produces gives a single cell result and it works well with one exception
The number of records in the data source varies, so the number of columns needing to be merged varies - and as these are hard wired they get missed if more columns added and query fails if less columns available to merge.
Is there an easy way to fix?
let
Source = Excel.CurrentWorkbook(){[Name="ZRPRQ_All"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Plant", Int64.Type}, {"Itar Flag", type text}, {"Req.Track No", type text}, {"Doc.Type", type text}, {"Item Cat", type text}, {"Req.No.", Int64.Type}, {"Pur.Grp", type text}, {"Desired Vendor Name", type text}, {"Fixed Vendor Name", type text}, {"Material", type text}, {"Short Text", type text}, {"Rev", type text}, {"Item", Int64.Type}, {"Req.Date", type datetime}, {"MRP Need Date", type any}, {"Need Date", type datetime}, {"Open Qty", Int64.Type}, {"Req Age Date", Int64.Type}}),
#"Removed Duplicates1" = Table.Distinct(#"Changed Type", {"Material"}),
#"Removed Duplicates" = Table.Distinct(#"Removed Duplicates1", {"Req.No."}),
#"Filtered Rows" = Table.SelectRows(#"Removed Duplicates", each ([Material] <> null) and ([Pur.Grp] = "ZZZ")),
#"Merged Queries" = Table.NestedJoin(#"Filtered Rows",{"Material"},tblMRPC,{"Material"},"NewColumn",JoinKind.LeftOuter),
#"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"MRPC"}, {"NewColumn.MRPC"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded NewColumn",{{"NewColumn.MRPC", "MRPC"}}),
#"Sorted Rows" = Table.Sort(#"Renamed Columns",{{"Req.Date", Order.Ascending}}),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Sorted Rows", {{"Req Age Date", type text}}, "en-GB"),{"Material", "MRPC", "Req Age Date"},Combiner.CombineTextByDelimiter(" - ", QuoteStyle.None),"Merged"),
#"Added Suffix" = Table.TransformColumns(#"Merged Columns", {{"Merged", each _ & "days", type text}}),
#"Removed Columns1" = Table.RemoveColumns(#"Added Suffix",{"Short Text", "Req.Date", "Need Date", "Open Qty"}),
#"Removed Other Columns" = Table.SelectColumns(#"Removed Columns1",{"Merged"}),
#"Transposed Table" = Table.Transpose(#"Removed Other Columns"),
#"Merged Columns1" = Table.CombineColumns(#"Transposed Table",{"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged"),
#"Replaced Value" = Table.ReplaceValue(#"Merged Columns1",",","#(lf)",Replacer.ReplaceText,{"Merged"})
in
#"Replaced Value"
Thanks
The number of records in the data source varies, so the number of columns needing to be merged varies - and as these are hard wired they get missed if more columns added and query fails if less columns available to merge.
Is there an easy way to fix?
let
Source = Excel.CurrentWorkbook(){[Name="ZRPRQ_All"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Plant", Int64.Type}, {"Itar Flag", type text}, {"Req.Track No", type text}, {"Doc.Type", type text}, {"Item Cat", type text}, {"Req.No.", Int64.Type}, {"Pur.Grp", type text}, {"Desired Vendor Name", type text}, {"Fixed Vendor Name", type text}, {"Material", type text}, {"Short Text", type text}, {"Rev", type text}, {"Item", Int64.Type}, {"Req.Date", type datetime}, {"MRP Need Date", type any}, {"Need Date", type datetime}, {"Open Qty", Int64.Type}, {"Req Age Date", Int64.Type}}),
#"Removed Duplicates1" = Table.Distinct(#"Changed Type", {"Material"}),
#"Removed Duplicates" = Table.Distinct(#"Removed Duplicates1", {"Req.No."}),
#"Filtered Rows" = Table.SelectRows(#"Removed Duplicates", each ([Material] <> null) and ([Pur.Grp] = "ZZZ")),
#"Merged Queries" = Table.NestedJoin(#"Filtered Rows",{"Material"},tblMRPC,{"Material"},"NewColumn",JoinKind.LeftOuter),
#"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"MRPC"}, {"NewColumn.MRPC"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded NewColumn",{{"NewColumn.MRPC", "MRPC"}}),
#"Sorted Rows" = Table.Sort(#"Renamed Columns",{{"Req.Date", Order.Ascending}}),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Sorted Rows", {{"Req Age Date", type text}}, "en-GB"),{"Material", "MRPC", "Req Age Date"},Combiner.CombineTextByDelimiter(" - ", QuoteStyle.None),"Merged"),
#"Added Suffix" = Table.TransformColumns(#"Merged Columns", {{"Merged", each _ & "days", type text}}),
#"Removed Columns1" = Table.RemoveColumns(#"Added Suffix",{"Short Text", "Req.Date", "Need Date", "Open Qty"}),
#"Removed Other Columns" = Table.SelectColumns(#"Removed Columns1",{"Merged"}),
#"Transposed Table" = Table.Transpose(#"Removed Other Columns"),
#"Merged Columns1" = Table.CombineColumns(#"Transposed Table",{"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged"),
#"Replaced Value" = Table.ReplaceValue(#"Merged Columns1",",","#(lf)",Replacer.ReplaceText,{"Merged"})
in
#"Replaced Value"
Thanks