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 the same applies for all the other dates.
Thanks ina advance.
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
6 Replies
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 Custom1Thanks for your reply , is this works with Power query editor in excel ?. I tried with code and logic giving some syntax errors.
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