Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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.

Imke Feldmann (The BIccountant)

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

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

12 REPLIES 12
Mariusz
Community Champion
Community Champion

Hi @Anonymous 

 

Assuming all lists are empty you can do something like below.

= Table.AddColumn(Source, "Records", each if Value.Is( [Custom], type record ) then [Custom] else null )
Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
Mariusz Repczynski

 

ImkeF
Super User
Super User

Hi @Anonymous ,

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-and-power-query-in-excel/

Otherwise please let me know where you're stuck.

Imke Feldmann (The BIccountant)

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

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Anonymous
Not applicable

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?

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.

 

Imke Feldmann (The BIccountant)

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

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Anonymous
Not applicable

Hi @ImkeF ,

 

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

Capture.JPG

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.

 

Imke Feldmann (The BIccountant)

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

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Anonymous
Not applicable

@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"

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.

Imke Feldmann (The BIccountant)

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

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Anonymous
Not applicable

@ImkeF Everything is working fine, but I found out that I'm unable to filter through other visualizations via the expanded qualifications. I've already looked at the interactions and everything seems fine. 

 

picture1.JPG

The strange thing is; if I filter on another visualization the qualifications will filter, as you can see below. 

picture2.JPG

 

 

Could you think of a solution? Thanks in advance!

Anonymous
Not applicable

@ImkeF ,

 

It's working thanks! 

Hi @Anonymous 

 

Would you be able to create a small sample?

 

Best Regards,
Mariusz

Please feel free to connect with me.
Mariusz Repczynski

 

Anonymous
Not applicable

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. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors