Forum Discussion
Solved
Want to extract partial data from previous rows
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 th...
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
Apologies for the confusion. Attached input and expected results .
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