ETL trick in power query

[buchta]

Hi 

I have a question with a litle challange 

i have data like this.. 

Order      Name   Type        ship      From                     To                        Days

001

ABC

cdc

bcb

20/2/2022

25/2/2022

6

and would like to end up with something like this

Order       Name      Type        ship      Date

001	ABC	cdc	bcb	20/2/2022
001	ABC	cdc	bcb	21/2/2022
001	ABC	cdc	bcb	22/2/2022
001	ABC	cdc	bcb	23/2/2022
001	ABC	cdc	bcb	24/2/2022
001	ABC	cdc	bcb	25/2/2022

what is the best way to achieve this in power query where i am using Direc Query as the connection mode
the DQ is the challange as I would prefer the query not to break. 🙂

my thoughts so far went to create table with dates only and then add some dynamic logic that would add new column everytime there is new ship .. and then unpivot al this and remove records where there is 0 value .. but that doesnt sounds right. and was breaking 

thank you for your help.. 

[amitchandak · Accepted solution]

@buchta , I doubt that you can do this in Direct Query mode, we can generate a list and add that dates and expand. But I doubt that will work in Direct Query

[CNENFRNL · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJ0cgaSySnJQDIpOQlIGhnoG+kbGRgZgdimMHasTrQSSMTF1Q1IFhcXA8mc/ByQGiMk9RZw9bEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Order = _t, Name = _t, Type = _t, ship = _t, From = _t, To = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Order", Int64.Type}, {"Name", type text}, {"Type", type text}, {"ship", type text}, {"From", type date}, {"To", type date}}, "fr"),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Span", each List.Transform({Number.From([From])..Number.From([To])}, Date.From)),
    #"Expanded Span" = Table.ExpandListColumn(#"Added Custom", "Span")
in
    #"Expanded Span"

[amitchandak · Accepted solution]

@buchta , I doubt that you can do this in Direct Query mode, we can generate a list and add that dates and expand. But I doubt that will work in Direct Query