topic Re: Want to extract partial data from previous rows in Power Query

Want to extract partial data from previous rows

Vara — Wed, 18 Aug 2021 10:05:36 GMT

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.

Re: Want to extract partial data from previous rows

Jakinta — Wed, 18 Aug 2021 14:57:09 GMT

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

Re: Want to extract partial data from previous rows

Vara — Wed, 18 Aug 2021 16:09:45 GMT

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

Re: Want to extract partial data from previous rows

Vara — Wed, 18 Aug 2021 16:24:29 GMT

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

Re: Want to extract partial data from previous rows

Jakinta — Wed, 18 Aug 2021 18:23:41 GMT

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

Re: Want to extract partial data from previous rows

Vara — Thu, 19 Aug 2021 13:32:27 GMT

Apologies for the confusion. Attached input and expected results .

Re: Want to extract partial data from previous rows

Jakinta — Fri, 20 Aug 2021 03:19:29 GMT

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