Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello
I have a data like
check No | Name | Date | |
34 | A | 12 Jan 2021 | |
12 Feb 2021 | |||
12 Mar 2021 | |||
35 | B | 13 Jan 2021 | |
13 Feb 2021 | |||
13 Mar 2021 |
I need to transform this data to
check No | Name | Date | Open | Close | |
34 | A | 12 Jan 2021 | 12 Jan 2021 | 12 Feb 2021 | 12 Feb 2021 |
12 Feb 2021 | false | false | false | ||
12 Feb 2021 | false | false | false | ||
35 | B | 13 Jan 2021 | 13 Jan 2021 | 13 Feb 2021 | 13 Mar 2021 |
13 Feb 2021 | false | false | false | ||
13 Mar 2021 | false | false | false |
Please help me to get this logic.
Thanks in Advance.
James Bond
Solved! Go to Solution.
Hi @Anonymous ,
Outputresult:
It feels a little cumbersome, mainly because I don't understand the exact logic.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSs5ITc5W8MtX0lHyS8xNBVIuiSWpSrE60UrGJkCeIxAbGil4JeYpGBkYGWJIuKUmYZfwTSxCkjAFCjqBJIzRjEKSQDUKSQJhVCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"check No", Int64.Type}, {"Name", type text}, {"Date", type date}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
#"Removed Columns" = Table.RemoveColumns(#"Added Index",{"Index"}),
#"Grouped Rows" = Table.Group(#"Removed Columns", {"check No"}, {{"allrows", each _, type table [check No=nullable number, Name=nullable text, Date=nullable date]}}),
#"Added Index1" = Table.AddColumn(#"Grouped Rows", "newrank", each Table.AddIndexColumn([allrows], "newrank", 1, 1)),
#"Expanded newrank" = Table.ExpandTableColumn(#"Added Index1", "newrank", {"check No", "Name", "Date", "newrank"}, {"newrank.check No", "newrank.Name", "newrank.Date", "newrank.newrank"}),
#"Removed Columns1" = Table.RemoveColumns(#"Expanded newrank",{"allrows", "newrank.check No"}),
#"Added Custom" = Table.AddColumn(#"Removed Columns1", "date", each [newrank.Date]),
#"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"check No", "newrank.Name", "date", "newrank.Date", "newrank.newrank"}),
#"Merged Queries" = Table.NestedJoin(#"Reordered Columns", {"newrank.newrank"}, index, {"index"}, "index", JoinKind.LeftOuter),
#"Expanded index" = Table.ExpandTableColumn(#"Merged Queries", "index", {"index", "name"}, {"index.index", "index.name"}),
#"Removed Columns2" = Table.RemoveColumns(#"Expanded index",{"index.index"}),
#"Reordered Columns1" = Table.ReorderColumns(#"Removed Columns2",{"check No", "newrank.Name", "date", "index.name", "newrank.Date"}),
#"Sorted Rows" = Table.Sort(#"Reordered Columns1",{{"check No", Order.Ascending}}),
#"Reordered Columns2" = Table.ReorderColumns(#"Sorted Rows",{"check No", "newrank.Name", "date", "index.name", "newrank.Date", "newrank.newrank"}),
#"Pivoted Column" = Table.Pivot(#"Reordered Columns2", List.Distinct(#"Reordered Columns2"[index.name]), "index.name", "newrank.Date"),
#"Filled Up" = Table.FillUp(#"Pivoted Column",{"close", "print"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Filled Up",{{"open", type text}, {"close", type text}, {"print", type text}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type1", "closenew", each if [newrank.newrank]=1 then [close] else "false"),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "printnew", each if [newrank.newrank]=1 then [print] else "false"),
#"Removed Columns3" = Table.RemoveColumns(#"Added Custom2",{"close", "print"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns3",{{"closenew", "close"}, {"printnew", "print"}}),
#"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns",{{"open", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type2",null,"false",Replacer.ReplaceValue,{"open"})
in
#"Replaced Value"
Did I answer your question? Mark my post as a solution!
Best Regards
Lucien
Hi @Anonymous ,
Outputresult:
It feels a little cumbersome, mainly because I don't understand the exact logic.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSs5ITc5W8MtX0lHyS8xNBVIuiSWpSrE60UrGJkCeIxAbGil4JeYpGBkYGWJIuKUmYZfwTSxCkjAFCjqBJIzRjEKSQDUKSQJhVCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"check No", Int64.Type}, {"Name", type text}, {"Date", type date}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
#"Removed Columns" = Table.RemoveColumns(#"Added Index",{"Index"}),
#"Grouped Rows" = Table.Group(#"Removed Columns", {"check No"}, {{"allrows", each _, type table [check No=nullable number, Name=nullable text, Date=nullable date]}}),
#"Added Index1" = Table.AddColumn(#"Grouped Rows", "newrank", each Table.AddIndexColumn([allrows], "newrank", 1, 1)),
#"Expanded newrank" = Table.ExpandTableColumn(#"Added Index1", "newrank", {"check No", "Name", "Date", "newrank"}, {"newrank.check No", "newrank.Name", "newrank.Date", "newrank.newrank"}),
#"Removed Columns1" = Table.RemoveColumns(#"Expanded newrank",{"allrows", "newrank.check No"}),
#"Added Custom" = Table.AddColumn(#"Removed Columns1", "date", each [newrank.Date]),
#"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"check No", "newrank.Name", "date", "newrank.Date", "newrank.newrank"}),
#"Merged Queries" = Table.NestedJoin(#"Reordered Columns", {"newrank.newrank"}, index, {"index"}, "index", JoinKind.LeftOuter),
#"Expanded index" = Table.ExpandTableColumn(#"Merged Queries", "index", {"index", "name"}, {"index.index", "index.name"}),
#"Removed Columns2" = Table.RemoveColumns(#"Expanded index",{"index.index"}),
#"Reordered Columns1" = Table.ReorderColumns(#"Removed Columns2",{"check No", "newrank.Name", "date", "index.name", "newrank.Date"}),
#"Sorted Rows" = Table.Sort(#"Reordered Columns1",{{"check No", Order.Ascending}}),
#"Reordered Columns2" = Table.ReorderColumns(#"Sorted Rows",{"check No", "newrank.Name", "date", "index.name", "newrank.Date", "newrank.newrank"}),
#"Pivoted Column" = Table.Pivot(#"Reordered Columns2", List.Distinct(#"Reordered Columns2"[index.name]), "index.name", "newrank.Date"),
#"Filled Up" = Table.FillUp(#"Pivoted Column",{"close", "print"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Filled Up",{{"open", type text}, {"close", type text}, {"print", type text}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type1", "closenew", each if [newrank.newrank]=1 then [close] else "false"),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "printnew", each if [newrank.newrank]=1 then [print] else "false"),
#"Removed Columns3" = Table.RemoveColumns(#"Added Custom2",{"close", "print"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns3",{{"closenew", "close"}, {"printnew", "print"}}),
#"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns",{{"open", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type2",null,"false",Replacer.ReplaceValue,{"open"})
in
#"Replaced Value"
Did I answer your question? Mark my post as a solution!
Best Regards
Lucien
User | Count |
---|---|
96 | |
85 | |
77 | |
66 | |
63 |
User | Count |
---|---|
110 | |
95 | |
95 | |
64 | |
59 |