Want to extract partial data from previous rows

[Vara]

Hi all, 

Could you please help me on the example scenario attached ? I would like to fetch data as below 

Ex: for 01/08/2021 want to read first 2 columns from previous day  until Col3  and the same applies for all the other dates. 

Thanks ina advance. 

[Vara]

Also i would take the input from another excel sheet where we have these columns . Also attached is output from 

your query 

 

But i am expecting the out put as 

8/18/2021 null null 10 11 12
8/19/2021 13 14 20 21 22
8/20/2021 23 24 30 31 32
8/21/2021 33 34 40 41 42

[Jakinta]

Well that is different from your original post. Not he same logic applied...

[Vara]

Apologies for the confusion. Attached input and expected results .

[Jakinta · Accepted solution]

This should do your thing.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Pcq7DcMwDADRXVQbsMSPxMxieP81kmNANocr3vOMuFfcMmWNa6xJ8oQosfFeyT7FBPY/mMCkmMxiCtM8mMK02SpmMMuDGcyaSTGHeR7MYd5Mi23YzoNt2G5mxQ7s5MEO7DTzYgGLPFjA4sfeLw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Col1 = _t, Col2 = _t, Col3 = _t, Col4 = _t, Col5 = _t]),
    Custom1 = let l=Table.ToColumns(Source), x=List.FirstN(l,1), y=List.Transform( List.LastN(l,2), each {null} & List.RemoveLastN(_,1)), z= List.RemoveFirstN (List.RemoveLastN(l,2),1) in Table.FromColumns( x & y & z , List.FirstN(Table.ColumnNames(Source),1) & List.Transform( List.Skip( List.Positions(l)), each "Column " & Text.From(_)))

in
    Custom1

[Vara]

Thanks for your reply , is this works with Power query editor in excel ?. I tried with code and logic giving some syntax errors. 

[Jakinta]

Here is one way to do it.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Pcq7DcMwDADRXVQbsMSPxMxieP81kmNANocr3vOMuFfcMmWNa6xJ8oQosfFeyT7FBPY/mMCkmMxiCtM8mMK02SpmMMuDGcyaSTGHeR7MYd5Mi23YzoNt2G5mxQ7s5MEO7DTzYgGLPFjA4sfeLw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Col1 = _t, Col2 = _t, Col3 = _t, Col4 = _t, Col5 = _t]),
    Custom1 = let l=Table.ToColumns(Source), x=List.FirstN(l,1)&List.LastN(l,2), y=List.Difference(l,x) in Table.FromColumns(List.Transform(x, List.Skip) & List.Transform(y, List.RemoveLastN),List.FirstN(Table.ColumnNames(Source),1) &List.Transform(List.Skip(List.Positions(l)), each "Column " & Text.From(_)))
in
    Custom1