Forum Discussion
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}})
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 ResultAdded the last step - referincing the table from the previous step.
12 Replies
- ImkeF
Community Champion
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.
Hi ImkeF ,
Thanks for your swift reply. Unfortunately, it still prompts me the same error. I've tried all the commands/codes below.
SpoilerExpression.Error: We cannot convert a value of type List to type Record.
Details:
Value=List
Type=TypeTable.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 MyJsonTableAny other ideas?
- ImkeF
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.
- Mariusz
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.
