Forum Discussion
QZ
3 years agoHelper I
(M language) How to create new column base on another column conditial value in same table in Query
i have original data like bellow:
How to transform to bellow in Power Query?
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtY31DdU0lHyTUzOyMxLVQCxDfUMlWJ1sEobAdlGuKWNgWxjVGkjDMONcElDDMcpDTEcRdoYzXA0u43RDMejG2K4sVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Process Date" = _t, Machine = _t, #"Electricity at 00:00:00" = _t]), groups = Table.Group(Source, {"Machine"}, {{"rows", each Table.Sort(_, "Process Date")}}), f = (lst as list) as list => let positions = List.Positions(lst), new_col = List.Accumulate( positions, {}, (s, c) => s & {try lst{c + 1} otherwise null} ) in new_col, add_column = Table.TransformColumns( groups, { "rows", (x) => let next_day = f( x[#"Electricity at 00:00:00"] ), new_tbl = Table.FromColumns(Table.ToColumns(x) & {next_day}, Table.ColumnNames(x) & {"Electricity at 00:00:00(Next Day)"}) in new_tbl } ), combine = Table.Sort(Table.Combine(add_column[rows]), {"Process Date"}) in combine
3 Replies
- QZHelper I
If you can shed some light, it would by much appreciated.
- AlienSxSuper User
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtY31DdU0lHyTUzOyMxLVQCxDfUMlWJ1sEobAdlGuKWNgWxjVGkjDMONcElDDMcpDTEcRdoYzXA0u43RDMejG2K4sVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Process Date" = _t, Machine = _t, #"Electricity at 00:00:00" = _t]), groups = Table.Group(Source, {"Machine"}, {{"rows", each Table.Sort(_, "Process Date")}}), f = (lst as list) as list => let positions = List.Positions(lst), new_col = List.Accumulate( positions, {}, (s, c) => s & {try lst{c + 1} otherwise null} ) in new_col, add_column = Table.TransformColumns( groups, { "rows", (x) => let next_day = f( x[#"Electricity at 00:00:00"] ), new_tbl = Table.FromColumns(Table.ToColumns(x) & {next_day}, Table.ColumnNames(x) & {"Electricity at 00:00:00(Next Day)"}) in new_tbl } ), combine = Table.Sort(Table.Combine(add_column[rows]), {"Process Date"}) in combine- QZHelper I
It works, thx!
Let me study the code first, anyway, thank you!