Forum Discussion

QZ's avatar
QZ
Helper I
3 years ago
Solved

(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

  • If you can shed some light, it would by much appreciated.

  • 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
    • QZ's avatar
      QZ
      Helper I

      It works, thx!
      Let me study the code first, anyway, thank you!