Split each row into 4 rows

[rouzbehrasai]

Hello. 

I want to split every row by 4 and for each row I want the column "sannsynlig dato for utbetaling av tiltak" be added 90 days.  And for each row I want the column "waken_adjofannualnoxreduction" to be devided by 4. Please see the image below for better understading of the problem. And see attached the PBIX file. 

Can I do this in Power Query? 

Here is the PBIX file: https://www.dropbox.com/s/thq9aa729de8qmi/Likviditet%20og%20KPI%20%281%29.pbix?dl=0

Thank you.

RR 

[rouzbehrasai]

Hello.

Thank you so much for your help.

I was wondering if you can help me futher to fix my problem. When I used the code you sent me and I changed it in order to fit my data, I get a expression.syntaxerror. Could this be because the table in your example is based on two set of data (columns) while mine is much more. Do I have to define all those colums? Here is the code I use. 

let
    Source = OData.Feed("https://nox.api.crm4.dynamics.com/api/data/v9.2/", null, [Implementation="2.0"]),
    Egendefinert1 = Source{[Name="waken_applications",Signature="table"]}[Data],
    #"Utvidet waken_lastdeclaration" = Table.ExpandRecordColumn(Egendefinert1, "waken_lastdeclaration", {"waken_expectedannualnoxreduction", "waken_verifiedreductionintaxablenoxemission", "waken_calcreductioninnoxemission", "waken_accumulatedreductioninemissions", "waken_paymentdate"}, {"waken_lastdeclaration.waken_expectedannualnoxreduction", "waken_lastdeclaration.waken_verifiedreductionintaxablenoxemission", "waken_lastdeclaration.waken_calcreductioninnoxemission", "waken_lastdeclaration.waken_accumulatedreductioninemissions", "waken_lastdeclaration.waken_paymentdate"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Utvidet waken_lastdeclaration",{"waken_portal_objectname",  "waken_uniqueidimo", "waken_portal_companyname",  "waken_portal_organizationnumber", "waken_measuretypeglobal", "waken_descriptionofthemeasure", "waken_grantedsupport", "waken_ertiltaketalttilannensknadinklanbud", "waken_applicationstatus", "waken_activitygrowthorcontinuation", "waken_amountpaid", "waken_amountpaid_date", "waken_scheduledstartupofmeasures", "waken_recommendedsupportafterprtotheboard", "waken_resolution", "waken_commentsfromthenoxfund", "waken_name", "waken_noxid", "waken_adjofannualnoxreduction", "waken_annuallyreducednoxemission", "waken_noxreductionforsupport","waken_lastdeclaration.waken_expectedannualnoxreduction", "waken_lastdeclaration.waken_calcreductioninnoxemission", "waken_lastdeclaration.waken_verifiedreductionintaxablenoxemission", "waken_lastdeclaration.waken_accumulatedreductioninemissions", "waken_lastdeclaration.waken_paymentdate"}),
    #"Kolonner med nytt navn" = Table.RenameColumns(#"Removed Other Columns",{{"waken_amountpaid", "Total utbetaling [kr]"}, {"waken_grantedsupport", "Øvre grense for investeringsstøtte fra tilsagn [kr]"}, {"waken_ertiltaketalttilannensknadinklanbud", "Alternativ til annen søknad (inkl. anbud)"}, {"waken_recommendedsupportafterprtotheboard", "Utsatt støtte [kr]"}, {"waken_scheduledstartupofmeasures", "Tidspunkt for oppstart med tiltak i drift"}, {"waken_uniqueidimo", "IMO nummer"}, {"waken_portal_companyname", "Firma"}, {"waken_portal_organizationnumber", "Organisasjonsnummer"}, {"waken_portal_objectname", "Objekt"}, {"waken_noxid", "NOxID"}, {"waken_adjofannualnoxreduction", "waken_adjofannualnoxreduction"}, {"waken_annuallyreducednoxemission", "Omsøkt NOx-reduksjon (kg/år)"}, {"waken_lastdeclaration.waken_verifiedreductionintaxablenoxemission", "Verifisert reduksjon i skattepliktige NOx-utslipp"}, {"waken_lastdeclaration.waken_calcreductioninnoxemission", "Beregnet reduksjon i NOx-utslipp"}, {"waken_lastdeclaration.waken_expectedannualnoxreduction", "Forventet årlige NOx-reduksjoner"}, {"waken_noxreductionforsupport", "NOx-reduksjon til grunn for støtte (kg/år)"}, {"waken_lastdeclaration.waken_accumulatedreductioninemissions", "Akkumulert verifisert reduksjon i avgiftspliktige NOx-utslipp (kg)"}, {"waken_lastdeclaration.waken_paymentdate", "Utbetalingsdato"}, {"waken_measuretypeglobal", "waken_measuretypeglobal"}}),
    #"Endret type" = Table.TransformColumnTypes(#"Kolonner med nytt navn",{{"Tidspunkt for oppstart med tiltak i drift", type date}, {"NOx-reduksjon til grunn for støtte (kg/år)", Int64.Type}}),
    Egendefinert2 = Table.SelectRows(#"Endret type", each not List.MatchesAll({[Tidspunkt for oppstart med tiltak i drift],[IN_GUEST]}, each _ = 0)),
    #"Filtrerte rader" = Table.SelectRows(Egendefinert2, each ([waken_applicationstatus] = 621000005) and ([waken_activitygrowthorcontinuation] = 621000000) and ([#"NOx-reduksjon til grunn for støtte (kg/år)"] <> null and [#"NOx-reduksjon til grunn for støtte (kg/år)"] <> 0)),
    #"Added Custom" = #!"Table.AddColumn(#""Filtrerte rader"", ""Custom"", each let #(lf)sd = [Tidspunkt for oppstart med tiltak i drift],#(lf)amt = [NOx-reduksjon til grunn for støtte (kg/år)]#(lf)in #(lf)List.Transform ({0..3}, each [NewDate = Date.AddDays(sd, _*90), NewAmt = amt/4]))",
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Expanded Custom1" = Table.ExpandRecordColumn(#"Expanded Custom", "Custom", {"NewDate", "NewAmt"}, {"NewDate", "NewAmt"})
in
    #"Expanded Custom1"

 Here is the Pbix file. https://www.dropbox.com/s/ydqkm6itcvjy79k/Likviditet%20og%20KPI.pbix?dl=0

Best regards,

RR

[ppm1]

Here's one way to do it in the query editor.  To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WstA31DcyMDJR0lEyNDUwMFCK1YlWMkYIGhmbg0VjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Amt = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Amt", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let 
sd = [Date],
amt = [Amt]
in 
List.Transform ({0..3}, each [NewDate = Date.AddDays(sd, _*90), NewAmt = amt/4])),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Removed Other Columns", "Custom"),
    #"Expanded Custom1" = Table.ExpandRecordColumn(#"Expanded Custom", "Custom", {"NewDate", "NewAmt"}, {"NewDate", "NewAmt"})
in
    #"Expanded Custom1"

Pat