Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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}})
Solved! Go to 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
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 )
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
Hi @ImkeF ,
Thanks for your swift reply. Unfortunately, it still prompts me the same error. I've tried all the commands/codes below.
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
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
@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
@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.
The strange thing is; if I filter on another visualization the qualifications will filter, as you can see below.
Could you think of a solution? Thanks in advance!
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"