cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
THilverts Frequent Visitor
Frequent Visitor

Difficulties with Expanding columns with list and record values

Hi Guys, 

 

I've created a connection to an REST API of staff.cloud. So far everything worked easily and fine. But there is a column buttering me. This particular column is holding empty 'list' values and records with mutiple lines. I've already tried something with PreviousStep and so on but it didn't work out. And i'm still unable to expand the column. Any ideas? 

 

This is what i tried so far: 

Option 1: CombineValueFromRecords = Table.TransformColumns(PreviousStep,{{"Column1.qualifications", each Combiner.CombineTextByDelimiter(";")(List.Transform( _, each [value] ) ),type text }}),
Option 2: Tablefix = Table.TransformColumns(#"Column1.qualifications",{{"Qualifications", each try Combiner.CombineTextByDelimiter(";")(List.Transform( _ , each [value]))otherwise null, type text}})

Capture.JPG

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User III
Super User III

Re: Difficulties with Expanding columns with list and record values

Hi @THilverts ,

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-c...

 

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.

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark my post as a solution. Thanks!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




View solution in original post

12 REPLIES 12
Super User III
Super User III

Re: Difficulties with Expanding columns with list and record values

Hi @THilverts ,

please check if you can apply the solution from this post to your case: https://www.thebiccountant.com/2017/07/25/how-to-expand-a-column-that-cannot-be-expanded-in-power-bi...

Otherwise please let me know where you're stuck.

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark my post as a solution. Thanks!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




THilverts Frequent Visitor
Frequent Visitor

Re: Difficulties with Expanding columns with list and record values

Hi @ImkeF ,

 

Thanks for your swift reply. Unfortunately, it still prompts me the same error. I've tried all the commands/codes below.

 

Spoiler
Expression.Error: We cannot convert a value of type List to type Record.
Details:
Value=List
Type=Type
Table.TransformColumns(Source, {{#"Column1 qualifications", each if Value.Is(_, type record) then _ else [a=_] }} )
Table.TransformColumns(Source, {{#"Column1 qualifications", each if Value.Is(_, type list) then _ else {_} }} )
Table.TransformColumns(Source, {{#"Column1 qualifications", each if Value.Is(_, type table) then _ else #table({#"Column1 qualifications"}, {{_}} ) }} )
 let
  MyJsonRecord = Json.Document(Web.Contents("https://cpm-netherlands.staff.cloud/api/v1/employees", [Headers=[Authorization="Bearer <api-key>", ContentType="application/x-www-form-urlencoded"]])),
  MyJsonTable = Table.FromRecords( { MyJsonRecord } )
 
  in
  MyJsonTable

Any other ideas?

Super User III
Super User III

Re: Difficulties with Expanding columns with list and record values

You have to adapt it to your situation. Please try the following: 

 

Table.TransformColumns(Source, {{#"Column1 qualifications", each if Value.Is(_, type list) then [] else _ }} )

This creates an empty record if you have a list, otherwise it just keeps the record.

You probably have to click on ".. show more fields" if you expand that column and the first rows are those empty records.

 

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark my post as a solution. Thanks!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




THilverts Frequent Visitor
Frequent Visitor

Re: Difficulties with Expanding columns with list and record values

Hi @ImkeF ,

 

Still the same error, it doesn't seem like it is able to expand. See the picture below.

Capture.JPG

Super User IV
Super User IV

Re: Difficulties with Expanding columns with list and record values

Hi @THilverts 

 

Would you be able to create a small sample?

 

Best Regards,
Mariusz

Please feel free to connect with me.
Mariusz Repczynski

 

Super User III
Super User III

Re: Difficulties with Expanding columns with list and record values

the reason for this errror-message probably lies in how you incorporated the code into your existing solution.

Could it be that your Source-step is actually a list?

 

Also, you've got an error in how you used my code. You should check if Value.Is(_, type list) and not type record.

 

It might help if you post your full M-code.

 

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark my post as a solution. Thanks!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




THilverts Frequent Visitor
Frequent Visitor

Re: Difficulties with Expanding columns with list and record values

let
    Source = Json.Document(Web.Contents("https://cpm-netherlands.staff.cloud/api/v1/employees", [Headers=[Authorization="Bearer <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"),
    #"Column1 qualifications" = #"Added Conditional Column"[Column1.qualifications],
    #"Converted to Table1" = Table.TransformColumns(Source, {{#"Column1 qualifications", each if Value.Is(_, type record) then [] else _ }} )
in
    #"Converted to Table1"

@ImkeF @Mariusz This is the complete code i'm using. 

THilverts Frequent Visitor
Frequent Visitor

Re: Difficulties with Expanding columns with list and record values

@ImkeF @Mariusz This is the full M-code:

 

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")
in
    #"Added Conditional Column"
Super User III
Super User III

Re: Difficulties with Expanding columns with list and record values

Hi @THilverts ,

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-c...

 

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.

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark my post as a solution. Thanks!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




View solution in original post

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors