Need help on Splitting the Data

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