Copy and paste entire column value to another column

[GokilaRaviraj]

Hi ,

Currently my input table looks like this,

I am fetching current month + remaining months of the year+ next year all the months. This is the logic to fetch months every time.  These months I am fetching it from calendar table and appending here.

In my original table, i have months till Dec_NFY.

Now Estimate q1 value has to be copied to Where ever my table has Jan, Feb, Mar 

Estimate q2 value has be copied to all the columns starts with Apr May jun 

Same logic goes to Q3 and Q4 as well.

Sample output.

I am trying to do with this code.

#"Replacevalue" = Table.ReplaceValue(#"Appended Query", each [Estimate Q1],
each if Text.StartsWith(_,"Jan") then [Estimate Q1] else 0 , Replacer.ReplaceText, {"Estimate Q1"})

This code is not working. 

Could anyone pls help me with this ?

Thanks in advance.

[dufoq3 · Accepted solution]

Hi @GokilaRaviraj, check this:

Output

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI0ABJGIMIYRJiACAWKcKxOtJITzUx2po3JsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [company = _t, #"estimate q1" = _t, #"estimate q2" = _t, #"estimate q3" = _t, #"estimate q4" = _t, Sep_cfy = _t, Oct_cfy = _t, Nov_cfy = _t, Dec_Cfy = _t, #"Jan _NFY" = _t, Feb_NFY = _t, Mar_NFY = _t, Apr_NFY = _t, May_NFY = _t, Jun_NFY = _t, Jul_NFY = _t, Aug_NFY = _t, Sep_NFY = _t, Oct_NFY = _t, Nov_NFY = _t, Dec_NFY = _t]),
    // You can probably delete this step when applying on real data.
    ReplacedValue = Table.ReplaceValue(Source," ",null,Replacer.ReplaceValue, Table.ColumnNames(Source)),
    MonthColNames = List.Buffer(List.Select(Table.ColumnNames(ReplacedValue), each List.Contains({"cfy", "nfy"}, _, (x,y)=> Text.EndsWith(y, x, Comparer.OrdinalIgnoreCase)))),
    ChangedType = Table.TransformColumnTypes(ReplacedValue,{{"estimate q1", type number}, {"estimate q2", type number}, {"estimate q3", type number}, {"estimate q4", type number}}),
    ReplacedValue1 = Table.ReplaceValue(ChangedType,null,"0",Replacer.ReplaceValue, MonthColNames),
    Unpivoted = Table.Unpivot(ReplacedValue1, MonthColNames, "Year_Month", "Value"),
    YearMonthCorrectFormat = Table.TransformColumns(Unpivoted, {{"Year_Month", each Text.Combine(List.Transform(List.ReplaceMatchingItems(Text.Split(_, "_"), {{"cfy", DateTime.ToText(DateTime.FixedLocalNow(), "yyyy")}, {"nfy", DateTime.ToText(Date.AddYears(DateTime.FixedLocalNow(), 1), "yyyy")}}, Comparer.OrdinalIgnoreCase), Text.Trim), "_"), type text}}),
    Ad_Quarter = Table.AddColumn(YearMonthCorrectFormat, "Quarter", each "q" & Text.From(Date.QuarterOfYear(Date.FromText("01_" & [Year_Month], [Format="dd_MMM_yyyy", Culture="en-US"]))), type text),
    Ad_QuarterValue = [ a = List.Buffer(Table.ColumnNames(Ad_Quarter)),
    b = Table.AddColumn(Ad_Quarter, "Quarter Value", each Record.ToList(Record.SelectFields(_, List.Select(a, (x)=> Text.EndsWith(x, [Quarter], Comparer.OrdinalIgnoreCase)))){0}?, type number)
  ][b],
    RemovedColumns = Table.RemoveColumns(Ad_QuarterValue,{"Value", "Quarter"}),
    Pivoted = Table.Pivot(RemovedColumns, List.Distinct(RemovedColumns[Year_Month]), "Year_Month", "Quarter Value", List.Sum)
in
    Pivoted

[Omid_Motamedise]

ou can not apply this formula because its replace based on the cell value not based on the column headers, it would be better to transform your table then apply this formula.

if you need help please provide your data here as table (not image) to provide you the code

[GokilaRaviraj]

Hi @Omid_Motamedise 

Here is my 

Sample Input

company

estimate q1

estimate q2

estimate q3

estimate q4

Sep_cfy

Oct_cfy

Nov_cfy

Dec_Cfy

Jan _NFY

Feb_NFY

Mar_NFY

Apr_NFY

May_NFY

Jun_NFY

Jul_NFY

Aug_NFY

Sep_NFY

Oct_NFY

Nov_NFY

Dec_NFY

A

10

20

30

40

B

10

20

30

40

C

10

20

30

40

This month logic gets changes every starting of the month as i mentioned above.

Sample output

company	estimate q1	estimate q2	estimate q3	estimate q4	Sep_cfy	Oct_cfy	Nov_cfy	Dec_Cfy	Jan _NFY	Feb_NFY	Mar_NFY	Apr_NFY	May_NFY	Jun_NFY	Jul_NFY	Aug_NFY	Sep_NFY	Oct_NFY	Nov_NFY	Dec_FY
A	10	20	30	40	30	40	40	40	10	10	10	20	20	20	30	30	30	40	40	40
B	10	20	30	40	30	40	40	40	10	10	10	20	20	20	30	30	30	40	40	40
C	10	20	30	40	30	40	40	40	10	10	10	20	20	20	30	30	30	40	40	40

[AnkitKukreja]

Hi! @GokilaRaviraj 

Your table should only have the below columns

and then you can use the below m-code. Go to Transform Data --> new source --> blank query --> advance editor and replace with this code.

let
Source = Table, // replace with your query name
AddSep_cfy = Table.AddColumn(Source, "Sep_cfy", each [estimate q3]),
AddOct_cfy = Table.AddColumn(AddSep_cfy, "Oct_cfy", each [estimate q4]),
AddNov_cfy = Table.AddColumn(AddOct_cfy, "Nov_cfy", each [estimate q4]),
AddDec_cfy = Table.AddColumn(AddNov_cfy, "Dec_cfy", each [estimate q4]),
AddJan_NFY = Table.AddColumn(AddDec_cfy, "Jan_NFY", each [estimate q1]),
AddFeb_NFY = Table.AddColumn(AddJan_NFY, "Feb_NFY", each [estimate q1]),
AddMar_NFY = Table.AddColumn(AddFeb_NFY, "Mar_NFY", each [estimate q1]),
AddApr_NFY = Table.AddColumn(AddMar_NFY, "Apr_NFY", each [estimate q2]),
AddMay_NFY = Table.AddColumn(AddApr_NFY, "May_NFY", each [estimate q2]),
AddJun_NFY = Table.AddColumn(AddMay_NFY, "Jun_NFY", each [estimate q2]),
AddJul_NFY = Table.AddColumn(AddJun_NFY, "Jul_NFY", each [estimate q3]),
AddAug_NFY = Table.AddColumn(AddJul_NFY, "Aug_NFY", each [estimate q3]),
AddSep_NFY = Table.AddColumn(AddAug_NFY, "Sep_NFY", each [estimate q3]),
AddOct_NFY = Table.AddColumn(AddSep_NFY, "Oct_NFY", each [estimate q4]),
AddNov_NFY = Table.AddColumn(AddOct_NFY, "Nov_NFY", each [estimate q4]),
AddDec_NFY = Table.AddColumn(AddNov_NFY, "Dec_NFY", each [estimate q4])
in
AddDec_NFY

[GokilaRaviraj]

Hi @AnkitKukreja 

Thanks for your reply. Anyhow these months you have added manually. My logic is different. These months populates dynamically.

Every new month, It is going to display, Current month + Remaining months of the year + Next year all the  months. 

So in this case, This won't work. I have found a solution for this. 

Custom1 = Table.AddColumn(#"Removed Other Columns", "month1", each Quarter1),
Custom2 = Table.AddColumn(#"Custom1", "month2", each Quarter2),
Custom3 = Table.AddColumn(#"Custom2", "month3", each Quarter3),
Custom4 = Table.AddColumn(#"Custom3", "month4", each Quarter4),

Custom5 = Table.AddColumn(#"Custom4", "EstimateQ1", each List.Repeat({[Estimate Q1]},10)),
Custom6 = Table.AddColumn(Custom5, "EstimateQ2", each List.Repeat({[Estimate Q2]},10)),
Custom7 = Table.AddColumn(#"Custom6", "EstimateQ3", each List.Repeat({[Estimate Q3]},10)),
Custom8 = Table.AddColumn(#"Custom7", "EstimateQ4", each List.Repeat({[Estimate Q4]},10)),

Custom9= Table.AddColumn(#"Custom8", "Custom1", each Table.FromRows(List.Zip({[month1], [EstimateQ1]}))),
Custom10= Table.AddColumn(Custom9, "Custom2", each Table.FromRows(List.Zip({[month2], [EstimateQ2]}))),
Custom11= Table.AddColumn(#"Custom10", "Custom3", each Table.FromRows(List.Zip({[month3], [EstimateQ3]}))),

#"Removed Columns" = Table.RemoveColumns(Custom11,{"Estimate Q1", "Estimate Q2", "Estimate Q3", "Estimate Q4"}),

Custom12 = Table.AddColumn(#"Removed Columns", "Custom4", each Table.FromRows(List.Zip({[month4], [EstimateQ4]}))),

Custom13 = Table.TransformColumns(Custom12, {{"Custom1", each Table.PromoteHeaders(Table.Transpose(_))}}),
Custom14 = Table.TransformColumns(Custom13, {{"Custom2", each Table.PromoteHeaders(Table.Transpose(_))}}),
Custom15 = Table.TransformColumns(#"Custom14", {{"Custom3", each Table.PromoteHeaders(Table.Transpose(_))}}),
Custom16 = Table.TransformColumns(#"Custom15", {{"Custom4", each Table.PromoteHeaders(Table.Transpose(_))}}),

This is my query. I have splitted the months based on quarter and put those months in list. 

Then I use, List.zip function to acheive this output. Please check. This may be helpful for someone.

[AnkitKukreja]

Hi! @GokilaRaviraj 

Please share sample data and expected output, that need to be achieved.

[GokilaRaviraj]

Sample Input

company

estimate q1

estimate q2

estimate q3

estimate q4

Sep_cfy

Oct_cfy

Nov_cfy

Dec_Cfy

Jan _NFY

Feb_NFY

Mar_NFY

Apr_NFY

May_NFY

Jun_NFY

Jul_NFY

Aug_NFY

Sep_NFY

Oct_NFY

Nov_NFY

Dec_NFY

A

10

20

30

40

B

10

20

30

40

C

10

20

30

40

This month logic gets changes every starting of the month as i mentioned above.

Sample output

company	estimate q1	estimate q2	estimate q3	estimate q4	Sep_cfy	Oct_cfy	Nov_cfy	Dec_Cfy	Jan _NFY	Feb_NFY	Mar_NFY	Apr_NFY	May_NFY	Jun_NFY	Jul_NFY	Aug_NFY	Sep_NFY	Oct_NFY	Nov_NFY	Dec_FY
A	10	20	30	40	30	40	40	40	10	10	10	20	20	20	30	30	30	40	40	40
B	10	20	30	40	30	40	40	40	10	10	10	20	20	20	30	30	30	40	40	40
C	10	20	30	40	30	40	40	40	10	10	10	20	20	20	30	30	30	40	40	40