Hi @Anonymous ,
This video show how to incorporate M-code into existing solutions: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/Power-BI-Forum-Help-How-to-integrate-M-code-into-your-existing/m-p/179314
For your case, it would probably look like so:
let
Source = Json.Document(Web.Contents("https://cpm-netherlands.staff.cloud/api/v1/employees", [Headers=[Authorization="<api-key>", ContentType="application/x-www-form-urlencoded"]])),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "wage_profile_id", "status", "created_at", "updated_at", "reminded_at", "last_logged_in_at", "last_active_at", "activated_at", "deactivated_at", "password_expires_at", "firstname", "lastname", "email", "mobile", "address_first", "address_second", "zip", "city", "country", "qualifications", "dynamic_field_26", "dynamic_field_28", "dynamic_field_29", "dynamic_field_30", "dynamic_field_31", "dynamic_field_32", "dynamic_field_34", "dynamic_field_36", "dynamic_field_38", "dynamic_field_39", "dynamic_field_40", "dynamic_field_41", "dynamic_field_42", "dynamic_field_43", "dynamic_field_44", "dynamic_field_45", "dynamic_field_47", "dynamic_field_48", "dynamic_field_70", "dynamic_field_71", "dynamic_field_72", "dynamic_field_73", "dynamic_field_74", "dynamic_field_75", "dynamic_field_76", "dynamic_field_77", "dynamic_field_78", "dynamic_field_79", "dynamic_field_80", "dynamic_field_81", "dynamic_field_82", "birthday", "dynamic_field_86", "dynamic_field_87", "dynamic_field_89", "gender", "dynamic_field_97", "dynamic_field_98", "dynamic_field_99", "dynamic_field_100", "dynamic_field_101", "dynamic_field_104", "dynamic_field_105", "dynamic_field_106", "dynamic_field_107", "dynamic_field_109", "dynamic_field_110", "dynamic_field_111", "dynamic_field_112", "dynamic_field_113", "dynamic_field_114", "dynamic_field_115", "dynamic_field_117", "dynamic_field_118", "dynamic_field_119", "dynamic_field_120", "dynamic_field_121", "dynamic_field_127", "dynamic_field_128", "dynamic_field_129"}, {"Column1.id", "Column1.wage_profile_id", "Column1.status", "Column1.created_at", "Column1.updated_at", "Column1.reminded_at", "Column1.last_logged_in_at", "Column1.last_active_at", "Column1.activated_at", "Column1.deactivated_at", "Column1.password_expires_at", "Column1.firstname", "Column1.lastname", "Column1.email", "Column1.mobile", "Column1.address_first", "Column1.address_second", "Column1.zip", "Column1.city", "Column1.country", "Column1.qualifications", "Column1.dynamic_field_26", "Column1.dynamic_field_28", "Column1.dynamic_field_29", "Column1.dynamic_field_30", "Column1.dynamic_field_31", "Column1.dynamic_field_32", "Column1.dynamic_field_34", "Column1.dynamic_field_36", "Column1.dynamic_field_38", "Column1.dynamic_field_39", "Column1.dynamic_field_40", "Column1.dynamic_field_41", "Column1.dynamic_field_42", "Column1.dynamic_field_43", "Column1.dynamic_field_44", "Column1.dynamic_field_45", "Column1.dynamic_field_47", "Column1.dynamic_field_48", "Column1.dynamic_field_70", "Column1.dynamic_field_71", "Column1.dynamic_field_72", "Column1.dynamic_field_73", "Column1.dynamic_field_74", "Column1.dynamic_field_75", "Column1.dynamic_field_76", "Column1.dynamic_field_77", "Column1.dynamic_field_78", "Column1.dynamic_field_79", "Column1.dynamic_field_80", "Column1.dynamic_field_81", "Column1.dynamic_field_82", "Column1.birthday", "Column1.dynamic_field_86", "Column1.dynamic_field_87", "Column1.dynamic_field_89", "Column1.gender", "Column1.dynamic_field_97", "Column1.dynamic_field_98", "Column1.dynamic_field_99", "Column1.dynamic_field_100", "Column1.dynamic_field_101", "Column1.dynamic_field_104", "Column1.dynamic_field_105", "Column1.dynamic_field_106", "Column1.dynamic_field_107", "Column1.dynamic_field_109", "Column1.dynamic_field_110", "Column1.dynamic_field_111", "Column1.dynamic_field_112", "Column1.dynamic_field_113", "Column1.dynamic_field_114", "Column1.dynamic_field_115", "Column1.dynamic_field_117", "Column1.dynamic_field_118", "Column1.dynamic_field_119", "Column1.dynamic_field_120", "Column1.dynamic_field_121", "Column1.dynamic_field_127", "Column1.dynamic_field_128", "Column1.dynamic_field_129"}),
#"Inserted Merged Column" = Table.AddColumn(#"Expanded Column1", "Stad en Land", each Text.Combine({[Column1.city], [Column1.country]}, ","), type text),
#"Renamed Columns" = Table.RenameColumns(#"Inserted Merged Column",{{"Column1.dynamic_field_43", "Admin?"}, {"Column1.dynamic_field_40", "Provincie"}}),
#"Inserted Merged Column1" = Table.AddColumn(#"Renamed Columns", "Voornaam + Achternaam", each Text.Combine({[Column1.firstname], [Column1.lastname]}, " "), type text),
#"Changed Type" = Table.TransformColumnTypes(#"Inserted Merged Column1",{{"Column1.created_at", type datetime}, {"Column1.qualifications", type any}}),
#"Inserted Month Name" = Table.AddColumn(#"Changed Type", "Month Name", each Date.MonthName([Column1.created_at]), type text),
#"Inserted Year" = Table.AddColumn(#"Inserted Month Name", "Year", each Date.Year([Column1.created_at]), Int64.Type),
#"Renamed Columns1" = Table.RenameColumns(#"Inserted Year",{{"Month Name", "Created On_Month Name"}, {"Year", "Created ON_Year"}}),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Renamed Columns1", {{"Created ON_Year", type text}}, "en-NL"),{"Created ON_Year", "Created On_Month Name"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Created On_month_year"),
#"Changed Type1" = Table.TransformColumnTypes(#"Merged Columns",{{"Created On_month_year", type date}}),
#"Renamed Columns2" = Table.RenameColumns(#"Changed Type1",{{"Column1.dynamic_field_28", "Auto"}}),
#"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns2",{{"Column1.birthday", type date}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Changed Type2", "Column1.birthday", "Column1.birthday - Copy"),
#"Inserted Age" = Table.AddColumn(#"Duplicated Column", "Age", each Date.From(DateTime.LocalNow()) - [#"Column1.birthday - Copy"], type duration),
#"Inserted Total Years" = Table.AddColumn(#"Inserted Age", "Total Years", each Duration.TotalDays([Age]) / 365, type number),
#"Removed Columns" = Table.RemoveColumns(#"Inserted Total Years",{"Age", "Total Years"}),
#"Inserted Age1" = Table.AddColumn(#"Removed Columns", "Age", each Date.From(DateTime.LocalNow()) - [#"Column1.birthday - Copy"], type duration),
#"Calculated Total Years" = Table.TransformColumns(#"Inserted Age1",{{"Age", each Duration.TotalDays(_) / 365, type number}}),
#"Inserted Round Down" = Table.AddColumn(#"Calculated Total Years", "Round Down", each Number.RoundDown([Age]), Int64.Type),
#"Renamed Columns3" = Table.RenameColumns(#"Inserted Round Down",{{"Round Down", "Leeftijd"}}),
#"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns3",{"Age", "Leeftijd", "Column1.birthday - Copy"}),
#"Inserted Age2" = Table.AddColumn(#"Removed Columns1", "Age", each Date.From(DateTime.LocalNow()) - [Column1.birthday], type duration),
#"Inserted Total Years1" = Table.AddColumn(#"Inserted Age2", "Total Years", each Duration.TotalDays([Age]) / 365, type number),
#"Inserted Round Down1" = Table.AddColumn(#"Inserted Total Years1", "Round Down", each Number.RoundDown([Total Years]), Int64.Type),
#"Renamed Columns4" = Table.RenameColumns(#"Inserted Round Down1",{{"Round Down", "Leeftijd"}}),
#"Changed Type3" = Table.TransformColumnTypes(#"Renamed Columns4",{{"Column1.updated_at", type datetime}, {"Column1.last_logged_in_at", type datetime}, {"Column1.last_active_at", type datetime}, {"Column1.activated_at", type datetime}, {"Column1.deactivated_at", type datetime}}),
#"Added Conditional Column" = Table.AddColumn(#"Changed Type3", "Leeftijdscategorie", each if [Leeftijd] <= 25 then "16-25" else if [Leeftijd] <= 45 then "26-45" else if [Leeftijd] <= 60 then "45-60" else if [Leeftijd] > 61 then "60-80" else "blank"),
Result = Table.TransformColumns(#"Added Conditional Column", {{#"Column1 qualifications", each if Value.Is(_, type list) then [] else _ }} )
in
Result
Added the last step - referincing the table from the previous step.
