Hello,
I am attempting to append multiple tables in power query. They are worksheets from the same workbook, so if there is a better way to append all the worksheets from a workbook, I am all ears. In any case, when I click on the "Appended Query" step, it shows all of the data from the worksheets properly appended, but by the end of the next step the data is only shown from the "Source" workbook.
Can anyone help me either fix the code or give me another code to automatically append all worksheets from a workbook?
let
Source = #"Alb PDX",
#"Appended Query" = Table.Combine({Source, #"Sfy PDX", #"Alb Sfy PDX REM", #"WinCo NW", #"WinCo NW REM", #"FM PDX", #"FM PDX REM", #"FM SEA", #"FM SEA REM", #"QFC", #"QFC REM"}),
WordsToReplace = Replacements[Original],
WordsToReplaceWith = Replacements[Replace],
ReplacementFunction = (InputText, Position) =>
let
cond_test = if (InputText is text and Text.Contains(InputText, WordsToReplace{Position})) then
InputText
else if Position = List.Count(WordsToReplace) - 1 then
InputText
else
WordsToReplace{Position},
replacement = if (InputText is text and Text.Contains(InputText, WordsToReplace{Position})) then
Text.Proper(WordsToReplaceWith{Position})
else
Text.Proper(InputText),
ReplaceText = Text.Replace(
InputText,
cond_test,
replacement)
in
if Position = List.Count(WordsToReplace) - 1
then
ReplaceText
else
@ReplacementFunction(ReplaceText, Position+1),
Output = Table.AddColumn(Source, "Brand", each ReplacementFunction([BRAND DESCRIPTION], 0)),
Renamed_Columns = Table.RenameColumns(Output,{{"MKT", "Market"}, {"PER", "Period"}, {"PROD", "Product Desc"}, {"UNIVERSAL PROD CODE", "UPC"}, {"SIZE", "Sz"}, {"SIZE DESCRIPTION", "Units"}, {"BE HIGH DESCRIPTION", "BE High"}, {"BE LOW DESCRIPTION", "BE Low"}, {"CATEGORY", "Cat"}, {"SUB-CATEGORY", "Sub-Cat"}, {"PRODUCT MODULE", "Prod Mod"}, {"PRODUCT", "Product Type"}, {"FLAVOR", "Flvr"}, {"Price Discount - Baseline Sales Dollars", "$ PDB"}, {"Price Discount - Baseline Sales Units", "U PDB"}, {"Price Discount - Percent ACV", "%ACV PD"}, {"Price Discount - Sales Dollars", "$ PD"}, {"Price Discount - Sales Units", "U PD"}, {"Any Feature and Display - Baseline Sales Dollars", "$ AFDB"}, {"Any Feature and Display - Baseline Sales Units", "U AFDB"}, {"Any Feature and Display - Percent ACV", "%ACV AFD"}, {"Any Feature and Display - Sales Dollars", "$ AFD"}, {"Any Feature and Display - Sales Units", "U AFD"}, {"Any Display without Feature - Baseline Sales Dollars", "$ ADB"}, {"Any Display without Feature - Baseline Sales Units", "U ADB"}, {"Any Display without Feature - Percent ACV", "%ACV AD"}, {"Any Display without Feature - Sales Dollars", "$ AD"}, {"Any Display without Feature - Sales Units", "U AD"}, {"Any Feature without Display - Baseline Sales Dollars", "$ AFB"}, {"Any Feature without Display - Baseline Sales Units", "U AFB"}, {"Any Feature without Display - Percent ACV", "%ACV AF"}, {"Any Feature without Display - Sales Dollars", "$ AF"}, {"Any Feature without Display - Sales Units", "U AF"}, {"Base No Promo $ Vol", "$ NPB"}, {"No Promo - Baseline Sales Units", "U NPB"}, {"No Promo - Percent ACV", "%ACV NP"}, {"No Promo - Sales Dollars", "$ NP"}, {"No Promo - Sales Units", "U NP"}}),
#"Replaced Value" = Table.ReplaceValue(Renamed_Columns,"'S","'s",Replacer.ReplaceText,{"Brand"})
in
#"Replaced Value"Thank you!
Bobby
