I was getting a lot of 429 type error from a Sharepoint site/list given the number of readings by PowerBI in a day, so I decided to use a Dataflow to connect to it (refreshed once a day) and feed my PowerBI reports from there.
However, I would like to keep some of the nested table columns from the Shaprepoint list (online in my case). PowerQuery Online is removing them automatically, no matter what I do.
The code here below gets generated by Powerquery upon saving and exiting my dataflow, to impose a type to the columns.
How can I get over this? Is it possible for my databaflow to contain columns of nested tables/records?
#"Transform columns" = Table.TransformColumnTypes(#"Expanded AttachmentFiles1", {{"FileSystemObjectType", type text}, {"Id", type text}, {"ServerRedirectedEmbedUri", type text}, {"ServerRedirectedEmbedUrl", type text}, {"NameId", type text}, {"NameStringId", type text}, {"Zone", type text}, {"Type de Visite", type text}, {"BLITZ", type text}, {"Non Conformite Batch", type text}, {"Personne 1 audit_x00Id", type text}, {"Personne 1 audit_x00StringId", type text}, {"Personne 2 audit_x00Id", type text}, {"Personne 2 audit_x00StringId", type text}, {"Description", type text}, {"Compliment", type text}, {"Title", type text}, {"Actions a realiser", type text}, {"Responsable de l_x00Id", type text}, {"Responsable de l_x00StringId", type text}, {"Status Action", type text}, {"Action Complete Targ", type text}, {"teamsConversationMetadata_D86AE2", type text}, {"ContentTypeId", type text}, {"ComplianceAssetId", type text}, {"AuthorId", type text}, {"Attachments", type text}, {"OData__ColorTag", type text}, {"ID.1", type text}, {"EditorId", type text}, {"OData__UIVersionString", type text}, {"GUID", type text}, {"AttachmentFiles.ServerRelativeUrl", type text}, {"Owner", type text}}),
#"Replace errors" = Table.ReplaceErrorValues(#"Transform columns", {{"FileSystemObjectType", null}, {"Id", null}, {"ServerRedirectedEmbedUri", null}, {"ServerRedirectedEmbedUrl", null}, {"NameId", null}, {"NameStringId", null}, {"Zone", null}, {"Type de Visite", null}, {"BLITZ", null}, {"Non Conformite Batch", null}, {"Personne 1 audit_x00Id", null}, {"Personne 1 audit_x00StringId", null}, {"Personne 2 audit_x00Id", null}, {"Personne 2 audit_x00StringId", null}, {"Description", null}, {"Compliment", null}, {"Title", null}, {"Actions a realiser", null}, {"Responsable de l_x00Id", null}, {"Responsable de l_x00StringId", null}, {"Status Action", null}, {"Action Complete Targ", null}, {"teamsConversationMetadata_D86AE2", null}, {"ContentTypeId", null}, {"ComplianceAssetId", null}, {"AuthorId", null}, {"Attachments", null}, {"OData__ColorTag", null}, {"ID.1", null}, {"EditorId", null}, {"OData__UIVersionString", null}, {"GUID", null}, {"AttachmentFiles.ServerRelativeUrl", null}, {"Owner", null}}),
#"Remove columns" = Table.RemoveColumns(#"Replace errors", Table.ColumnsOfType(#"Replace errors", {type table, type record, type list, type nullable binary, type binary, type function}))
in
#"Remove columns"
