Forum Discussion
Copy and paste entire column value to another column
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.
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
7 Replies
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
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 codeHere 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 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
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
