Added New Columns to data source Table - Hard time getting my previous steps updated

[Anonymous]

Hi,

I had a table that was working great where I had measures that had a CUR and YAG time element to it. I have now added a 2 YAG column for all of the measures in my Excel data source file.

My current query steps have been modified to take care of the new 2 YAG columns but once the steps are done is appears as though the steps are taking YAG and 2 YAG and adding them together.

Here is the code and I think that the issue is that when I am done I have 2 entries in the year column. One for CUR and one for YAG.

I need a 3rd entry that would be the 2 YAG: Can someone help me with this?

let
    Source = fctprirawtransformed,
    

    #"Removed Dimension Fields" = Table.SelectColumns(Source,{"SALES - CUR 04", "SALES - YAG 04", "SALES – 2 YAG 04", "SALES - CUR 12", "SALES - YAG 12", "SALES – 2 YAG 26", "SALES - CUR 26", "SALES - YAG 26", "SALES – 2 YAG 12", "SALES - CUR 52", "SALES - YAG 52", "SALES – 2 YAG 52", "SALES - CUR YTD", "SALES - YAG YTD", "SALES – 2 YAG YTD", "UNITS - CUR 04", "UNITS - YAG 04", "UNITS – 2 YAG 04", "UNITS - CUR 12", "UNITS - YAG 12", "UNITS – 2 YAG 26", "UNITS - CUR 26", "UNITS - YAG 26", "UNITS – 2 YAG 12", "UNITS - CUR 52", "UNITS - YAG 52", "UNITS – 2 YAG 52", "UNITS - CUR YTD", "UNITS - YAG YTD", "UNITS – 2 YAG YTD", "PROMO UNITS - CUR 04", "PROMO UNITS - YAG 04", "PROMO UNITS – 2 YAG 04", "PROMO UNITS - CUR 12", "PROMO UNITS - YAG 12", "PROMO UNITS – 2 YAG 26", "PROMO UNITS - CUR 26", "PROMO UNITS - YAG 26", "PROMO UNITS – 2 YAG 12", "PROMO UNITS - CUR 52", "PROMO UNITS - YAG 52", "PROMO UNITS – 2 YAG 52", "PROMO UNITS - CUR YTD", "PROMO UNITS - YAG YTD", "PROMO UNITS – 2 YAG YTD", "TDP - CUR 04", "TDP - YAG 04", "TDP – 2 YAG 04", "TDP - CUR 12", "TDP - YAG 12", "TDP – 2 YAG 26", "TDP - CUR 26", "TDP - YAG 26", "TDP – 2 YAG 12", "TDP - CUR 52", "TDP - YAG 52", "TDP – 2 YAG 52", "TDP - CUR YTD", "TDP - YAG YTD", "TDP – 2 YAG YTD", "MAX STORES SELLING - CUR 04", "MAX STORES SELLING - YAG 04", "MAX STORES SELLING – 2 YAG 04", "MAX STORES SELLING - CUR 12", "MAX STORES SELLING - YAG 12", "MAX STORES SELLING – 2 YAG 26", "MAX STORES SELLING - CUR 26", "MAX STORES SELLING - YAG 26", "MAX STORES SELLING – 2 YAG 12", "MAX STORES SELLING - CUR 52", "MAX STORES SELLING - YAG 52", "MAX STORES SELLING – 2 YAG 52", "MAX STORES SELLING - CUR YTD", "MAX STORES SELLING - YAG YTD", "MAX STORES SELLING – 2 YAG YTD", "USW - CUR 04", "USW - YAG 04", "USW – 2 YAG 04", "USW - CUR 12", "USW - YAG 12", "USW – 2 YAG 26", "USW - CUR 26", "USW - YAG 26", "USW – 2 YAG 12", "USW - CUR 52", "USW - YAG 52", "USW – 2 YAG 52", "USW - CUR YTD", "USW - YAG YTD", "USW – 2 YAG YTD", "SHIPPED UNITS - CUR 04", "SHIPPED UNITS - YAG 04", "SHIPPED UNITS - CUR 12", "SHIPPED UNITS - YAG 12", "SHIPPED UNITS - CUR 26", "SHIPPED UNITS - YAG 26", "SHIPPED UNITS - CUR 52", "SHIPPED UNITS - YAG 52", "SHIPPED UNITS - CUR YTD", "SHIPPED UNITS - YAG YTD", "STORE COUNT - CUR 04", "STORE COUNT - YAG 04", "STORE COUNT – 2 YAG 04", "STORE COUNT - CUR 12", "STORE COUNT - YAG 12", "STORE COUNT – 2 YAG 26", "STORE COUNT - CUR 26", "STORE COUNT - YAG 26", "STORE COUNT – 2 YAG 12", "STORE COUNT - CUR 52", "STORE COUNT - YAG 52", "STORE COUNT – 2 YAG 52", "STORE COUNT - CUR YTD", "STORE COUNT - YAG YTD", "STORE COUNT – 2 YAG YTD", "AVG TDP - CUR 04", "AVG TDP - YAG 04", "AVG TDP – 2 YAG 04", "AVG TDP - CUR 12", "AVG TDP - YAG 12", "AVG TDP – 2 YAG 26", "AVG TDP - CUR 26", "AVG TDP - YAG 26", "AVG TDP – 2 YAG 12", "AVG TDP - CUR 52", "AVG TDP - YAG 52", "AVG TDP – 2 YAG 52", "AVG TDP - CUR YTD", "AVG TDP - YAG YTD", "AVG TDP – 2 YAG YTD", "WTD USW - CUR 04", "WTD USW - YAG 04", "WTD USW – 2 YAG 04", "WTD USW - CUR 12", "WTD USW - YAG 12", "WTD USW – 2 YAG 26", "WTD USW - CUR 26", "WTD USW - YAG 26", "WTD USW – 2 YAG 12", "WTD USW - CUR 52", "WTD USW - YAG 52", "WTD USW – 2 YAG 52", "WTD USW - CUR YTD", "WTD USW - YAG YTD", "WTD USW – 2 YAG YTD", "COMP SALES - CUR 04", "COMP SALES - YAG 04", "COMP UNITS - CUR 04", "COMP UNITS - YAG 04", "COMP STORE COUNT - CUR 04", "COMP STORE COUNT - YAG 04", "MAX ACV - CUR 04", "MAX ACV - YAG 04", "COMP SALES - CUR 26", "COMP SALES - YAG 26", "COMP UNITS - CUR 26", "COMP UNITS - YAG 26", "COMP STORE COUNT - CUR 26", "COMP STORE COUNT - YAG 26", "MAX ACV - CUR 26", "MAX ACV - YAG 26", "COMP SALES - CUR 52", "COMP SALES - YAG 52", "COMP UNITS - CUR 52", "COMP UNITS - YAG 52", "COMP STORE COUNT - CUR 52", "COMP STORE COUNT - YAG 52", "MAX ACV - CUR 52", "MAX ACV - YAG 52", "WEEKS - CUR 04", "WEEKS - CUR 12", "WEEKS - CUR 26", "WEEKS - CUR 52", "WEEKS - CUR YTD", "INDEX"}),
    #"Get Column List" = Table.ColumnNames(#"Removed Dimension Fields"),
    #"Replace MAX ACV Columnname" = List.ReplaceValue(#"Get Column List", "MAX ACV", "ACV MAX", Replacer.ReplaceText),
    #"Replace 04 Week Columnname" = List.ReplaceValue(#"Replace MAX ACV Columnname","04","LATEST 04 WKS",Replacer.ReplaceText),
    #"Replace 12 Week Columnname" = List.ReplaceValue(#"Replace 04 Week Columnname", "12","LATEST 12 WKS", Replacer.ReplaceText),
    #"Replace 13 Week Columnname" = List.ReplaceValue(#"Replace 12 Week Columnname", "13","LATEST 13 WKS", Replacer.ReplaceText),
    #"Replace 26 Week Columnname" = List.ReplaceValue(#"Replace 13 Week Columnname", "26","LATEST 26 WKS", Replacer.ReplaceText),
    #"Replace 52 Week Columnname" = List.ReplaceValue(#"Replace 26 Week Columnname", "52","LATEST 52 WKS", Replacer.ReplaceText),
    #"Replace YTD Week Columnname" = List.ReplaceValue(#"Replace 52 Week Columnname", "YTD","YTD", Replacer.ReplaceText),
    



    #"Create New Columns List" = List.Zip({#"Get Column List",#"Replace YTD Week Columnname"}),
    #"Rename Columns" = Table.RenameColumns(#"Removed Dimension Fields", #"Create New Columns List"),

    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Rename Columns", {"INDEX"}, "Attribute", "VALUE"),


//  #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Dimension Fields", {"INDEX"}, "Attribute", "Value"),
    #"Create MEASURE Column" = Table.AddColumn(#"Unpivoted Other Columns", "MEASURE", each Text.BeforeDelimiter([Attribute], " -"), type text),
    #"Created CURYAG YEAR Column" = Table.AddColumn(#"Create MEASURE Column", "YEAR", each Text.BetweenDelimiters([Attribute], "- ", " "), type text),
    //  #"Filtered Rows" = Table.SelectRows(#"Created CURYAG YEAR Column", each ([YEAR] = "")),
//  #"Replaced Value" = Table.ReplaceValue(#"Filtered Rows","","CUR",Replacer.ReplaceValue,{"YEAR"}),
    #"Created TIME FRAME Column" = Table.AddColumn(#"Created CURYAG YEAR Column", "TIME FRAME", each Text.End([Attribute], 13), type text),
    //#"Filtered Rows1" = Table.SelectRows(#"Created TIME FRAME Column", each not Text.EndsWith([TIME FRAME], "YTD")),
    #"Update YTD" = Table.ReplaceValue(#"Created TIME FRAME Column",each [TIME FRAME],each if Text.EndsWith([TIME FRAME],"YTD") then "YTD" else [TIME FRAME],Replacer.ReplaceValue,{"TIME FRAME"}),
    //    #"Filtered Rows1" = Table.SelectRows(#"Replaced Value7", each true),

//    #"Replaced Value" = List.ReplaceValue(#"Created TIME FRAME Column","LES - CUR YTD","YTD",Replacer.ReplaceText),
//    #"Replaced Value1" = List.ReplaceValue(#"Replaced Value","LES - YAG YTD","YTD",Replacer.ReplaceText),
//    #"Replaced Value2" = List.ReplaceValue(#"Replaced Value1","ITS - CUR YTD","YTD",Replacer.ReplaceText),
//    #"Replaced Value3" = List.ReplaceValue(#"Replaced Value2","ITS - YAG YTD","YTD",Replacer.ReplaceText),
//    #"Replaced Value4" = List.ReplaceValue(#"Replaced Value3","MAX - CUR YTD","YTD",Replacer.ReplaceText),
//    #"Replaced Value5" = List.ReplaceValue(#"Replaced Value4","MAX - YAG YTD","YTD",Replacer.ReplaceText),
//    #"Replaced Value6" = List.ReplaceValue(#"Replaced Value5","USW - CUR YTD","YTD",Replacer.ReplaceText),
//    #"Replaced Value7" = List.ReplaceValue(#"Replaced Value6","USW - YAG YTD","YTD",Replacer.ReplaceText),
//  #"Renamed 04 TIME FRAME" = Table.ReplaceValue(#"Created TIME FRAME Column","04","LATEST 04 WKS",Replacer.ReplaceValue,{"TIME FRAME"}),
//  #"Renamed 12 TIME FRAME" = Table.ReplaceValue(#"Renamed 04 TIME FRAME","12","LATEST 12 WKS",Replacer.ReplaceValue,{"TIME FRAME"}),
//  #"Renamed 26 TIME FRAME" = Table.ReplaceValue(#"Renamed 12 TIME FRAME","26","LATEST 26 WKS",Replacer.ReplaceText,{"TIME FRAME"}),
//  #"Renamed 52 TIME FRAME" = Table.ReplaceValue(#"Renamed 26 TIME FRAME","52","LATEST 52 WKS",Replacer.ReplaceValue,{"TIME FRAME"}),
//  #"Removed Columns" = Table.RemoveColumns(#"Renamed 52 TIME FRAME",{"Attribute"}),
    #"Removed Columns" = Table.RemoveColumns( #"Update YTD", {"Attribute"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each true)
in
    #"Filtered Rows"

[v-angzheng-msft]

Hi, @Anonymous 

Could you please consider sharing more details about it and posting expected result so it is clear on what needs to be implemented?  And It would be great if there is a sample file without any sesentive information here.

It makes it easier to give you a solution.

1. Sample (dummy dataset) data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@v-angzheng-msft 

Thank you...yes I know that I need to be able to better explain what I am wanting to do:

Here is a sample of my raw data:

DATA SOURCE GEOGRAPHY	MAX DATE	UPC	DSMOCURR	DSMOYAG	DSMOYAG2
ABS CORP	12/30/2021	1284241001	$124,943	$0
ABS CORP	12/30/2021	1510800003	$144	$131
ABS CORP	12/30/2021	1510800005	$324	$164

The measure names get converted through the current steps of my query to give me the following output:

INDEX	VALUE	MEASURE	YEAR	TIME FRAME
1	124943.4511	SALES	CUR	LATEST 04 WKS
1	0	SALES	YAG	LATEST 04 WKS
1	369242.3804	SALES	CUR	LATEST 12 WKS
1	0	SALES	YAG	LATEST 12 WKS
1	796646.0414	SALES	CUR	LATEST 26 WKS
1	0	SALES	YAG	LATEST 26 WKS
1	852341.7549	SALES	CUR	LATEST 52 WKS
1	0	SALES	YAG	LATEST 52 WKS

The problem is the YAG is actually taking all of the YAG and YAG2 measures from my raw data and adding them together for the YAG rows in my output table.