Need help on Splitting the Data

[Anonymous]

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	Print
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

[v-luwang-msft · Accepted 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