Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
i have original data like bellow:
How to transform to bellow in Power Query?
Solved! Go to Solution.
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
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
It works, thx!
Let me study the code first, anyway, thank you!
If you can shed some light, it would by much appreciated.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.