CODEWITHSUNDEEP
csvpowerbipowerquery
SherlockSpreadsheets
SherlockSpreadsheets

Reputation: 2360

power query text truncated with Csv.Document

I am loading CSV file in Power Query editor (Excel desktop). The CSV file has 4 columns. Column 4 has text that is getting truncated when previewing the data in Power Query editor.

Any help or suggestions??

I tried reading some references. But nothing that clicked for me.

References

Chris Webb's BI Blog: What Is The Maximum Length Of A Text Value In Power BI? Chris Webb's BI Blog https://blog.crossjoin.co.uk/2019/05/17/maximum-length-text-value-power-bi/

Text truncated to 255 characters when importing from .xsls, but not from .csv https://social.technet.microsoft.com/Forums/windows/en-US/3a8bc142-019a-4e6e-b853-c3d4bfff1aea/text-truncated-to-255-characters-when-importing-from-xsls-but-not-from-csv?forum=powerquery

Csv.Document - PowerQuery M | Microsoft Docs https://learn.microsoft.com/en-us/powerquery-m/csv-document

Chris Webb's BI Blog: An In-Depth Look At The Csv.Document M Function Chris Webb's BI Blog https://blog.crossjoin.co.uk/2018/03/09/an-in-depth-look-at-the-csv-document-m-function/

CSV_(txt_value)

char count = 1262

"{""Id"":""3880a610-6c94-44aa-998c-f6c6b8ccbebc"",""RecordType"":20,""CreationTime"":""2021-09-20T20:54:27"",""Operation"":""ViewReport"",""OrganizationId"":""XXXXXXX-XXX-XXXXXXXXXXXXXXXXX"",""UserType"":0,""UserKey"":""1003200110B822A2"",""Workload"":""PowerBI"",""UserId"":""[email protected]"",""ClientIP"":""XX.XXX.XX.XXX"",""UserAgent"":""Mozilla\/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit\/537.36 (KHTML, like Gecko) Chrome\/93.0.4577.82 Safari\/537.36"",""Activity"":""ViewReport"",""ItemName"":""IT Spend Analysis Sample (RLS)"",""WorkSpaceName"":""DEMO 1 - ** TEST TEST **"",""DatasetName"":""IT Spend Analysis Sample (RLS)"",""ReportName"":""IT Spend Analysis Sample (RLS)"",""CapacityId"":""2C378C41-B989-4998-9FF8-046798A3572C"",""CapacityName"":""Premium Per User - Reserved"",""WorkspaceId"":""814483ee-dc75-47be-a379-db0efe658fb6"",""ObjectId"":""IT Spend Analysis Sample (RLS)"",""DatasetId"":""4506dbe0-5ecf-47f9-85a9-73ff278bca3f"",""ReportId"":""18d7271e-fb14-425c-a17b-a2ce4866dd48"",""IsSuccess"":true,""ReportType"":""PowerBIReport"",""RequestId"":""4073f095-fbb0-94bc-b05d-bffba3ec3566"",""ActivityId"":""bf26238f-4dd0-86c5-620c-20ab29c1704c"",""DistributionMethod"":""Workspace"",""ConsumptionMethod"":""Power BI Web""}"

PowerQuery_(txt_value):

char count = 1119

"{""Id"":""3880a610-6c94-44aa-998c-f6c6b8ccbebc"",""RecordType"":20,""CreationTime"":""2021-09-20T20:54:27"",""Operation"":""ViewReport"",""OrganizationId"":""XXXXXXX-XXX-XXXXXXXXXXXXXXXXX"",""UserType"":0,""UserKey"":""1003200110B822A2"",""Workload"":""PowerBI"",""UserId"":""[email protected]"",""ClientIP"":""XX.XXX.XX.XXX"",""UserAgent"":""Mozilla\/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit\/537.36 (KHTML, like Gecko) Chrome\/93.0.4577.82 Safari\/537.36"",""Activity"":""ViewReport"",""ItemName"":""IT Spend Analysis Sample (RLS)"",""WorkSpaceName"":""DEMO 1 - ** TEST TEST **"",""DatasetName"":""IT Spend Analysis Sample (RLS)"",""ReportName"":""IT Spend Analysis Sample (RLS)"",""CapacityId"":""2C378C41-B989-4998-9FF8-046798A3572C"",""CapacityName"":""Premium Per User - Reserved"",""WorkspaceId"":""814483ee-dc75-47be-a379-db0efe658fb6"",""ObjectId"":""IT Spend Analysis Sample (RLS)"",""DatasetId"":""4506dbe0-5ecf-47f9-85a9-73ff278bca3f"",""ReportId"":""18d7271e-fb14-425c-a17b-a2ce4866dd48"",""IsSuccess"":true,""ReportType"":""PowerBIReport"",""RequestId"":""4073f095-fbb0-94bc-b05d-b..."

Test.csv

CreationDate,UserIds,Operations,AuditData
2021-09-20T20:54:27.0000000Z,[email protected],ViewReport,"{""Id"":""3880a610-6c94-44aa-998c-f6c6b8ccbebc"",""RecordType"":20,""CreationTime"":""2021-09-20T20:54:27"",""Operation"":""ViewReport"",""OrganizationId"":""XXXXXXX-XXX-XXXXXXXXXXXXXXXXX"",""UserType"":0,""UserKey"":""1003200110B822A2"",""Workload"":""PowerBI"",""UserId"":""[email protected]"",""ClientIP"":""XX.XXX.XX.XXX"",""UserAgent"":""Mozilla\/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit\/537.36 (KHTML, like Gecko) Chrome\/93.0.4577.82 Safari\/537.36"",""Activity"":""ViewReport"",""ItemName"":""IT Spend Analysis Sample (RLS)"",""WorkSpaceName"":""DEMO 1 - ** TEST TEST **"",""DatasetName"":""IT Spend Analysis Sample (RLS)"",""ReportName"":""IT Spend Analysis Sample (RLS)"",""CapacityId"":""2C378C41-B989-4998-9FF8-046798A3572C"",""CapacityName"":""Premium Per User - Reserved"",""WorkspaceId"":""814483ee-dc75-47be-a379-db0efe658fb6"",""ObjectId"":""IT Spend Analysis Sample (RLS)"",""DatasetId"":""4506dbe0-5ecf-47f9-85a9-73ff278bca3f"",""ReportId"":""18d7271e-fb14-425c-a17b-a2ce4866dd48"",""IsSuccess"":true,""ReportType"":""PowerBIReport"",""RequestId"":""4073f095-fbb0-94bc-b05d-bffba3ec3566"",""ActivityId"":""bf26238f-4dd0-86c5-620c-20ab29c1704c"",""DistributionMethod"":""Workspace"",""ConsumptionMethod"":""Power BI Web""}"
2021-09-20T16:45:13.0000000Z,[email protected],ViewReport,"{""Id"":""bb4d22b6-d9dc-43ee-a10b-6cf45ccb03db"",""RecordType"":20,""CreationTime"":""2021-09-20T16:45:13"",""Operation"":""ViewReport"",""OrganizationId"":""XXXXXXX-XXX-XXXXXXXXXXXXXXXXX"",""UserType"":0,""UserKey"":""1003200110B822A2"",""Workload"":""PowerBI"",""UserId"":""[email protected]"",""ClientIP"":""XX.XXX.XX.XXX"",""UserAgent"":""Mozilla\/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit\/537.36 (KHTML, like Gecko) Chrome\/65.0.3325.181 Safari\/537.36"",""Activity"":""ViewReport"",""ItemName"":""IT Spend Analysis Sample (RLS)"",""WorkSpaceName"":""DEMO 1 - ** TEST TEST **"",""DatasetName"":""IT Spend Analysis Sample (RLS)"",""ReportName"":""IT Spend Analysis Sample (RLS)"",""CapacityId"":""2C378C41-B989-4998-9FF8-046798A3572C"",""CapacityName"":""Premium Per User - Reserved"",""WorkspaceId"":""814483ee-dc75-47be-a379-db0efe658fb6"",""ObjectId"":""IT Spend Analysis Sample (RLS)"",""DatasetId"":""4506dbe0-5ecf-47f9-85a9-73ff278bca3f"",""ReportId"":""18d7271e-fb14-425c-a17b-a2ce4866dd48"",""IsSuccess"":true,""ReportType"":""PowerBIReport"",""RequestId"":""b2a0095c-efc4-08a5-3348-7313c99c8c77"",""ActivityId"":""513cc6a6-a203-4cb4-afb7-a2fb170b1916"",""DistributionMethod"":""Workspace""}"
2021-09-20T16:45:12.0000000Z,[email protected],ViewReport,"{""Id"":""b8b49c6d-4daf-43cf-8e0a-15f36f39212a"",""RecordType"":20,""CreationTime"":""2021-09-20T16:45:12"",""Operation"":""ViewReport"",""OrganizationId"":""XXXXXXX-XXX-XXXXXXXXXXXXXXXXX"",""UserType"":0,""UserKey"":""1003200110B822A2"",""Workload"":""PowerBI"",""UserId"":""[email protected]"",""ClientIP"":""XX.XXX.XX.XXX"",""UserAgent"":""Mozilla\/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit\/537.36 (KHTML, like Gecko) Chrome\/65.0.3325.181 Safari\/537.36"",""Activity"":""ViewReport"",""ItemName"":""IT Spend Analysis Sample (RLS)"",""WorkSpaceName"":""DEMO 1 - ** TEST TEST **"",""DatasetName"":""IT Spend Analysis Sample (RLS)"",""ReportName"":""IT Spend Analysis Sample (RLS)"",""CapacityId"":""2C378C41-B989-4998-9FF8-046798A3572C"",""CapacityName"":""Premium Per User - Reserved"",""WorkspaceId"":""814483ee-dc75-47be-a379-db0efe658fb6"",""ObjectId"":""IT Spend Analysis Sample (RLS)"",""DatasetId"":""4506dbe0-5ecf-47f9-85a9-73ff278bca3f"",""ReportId"":""18d7271e-fb14-425c-a17b-a2ce4866dd48"",""IsSuccess"":true,""ReportType"":""PowerBIReport"",""RequestId"":""102f7153-27e6-a625-2337-c335e25706d1"",""ActivityId"":""513cc6a6-a203-4cb4-afb7-a2fb170b1916"",""DistributionMethod"":""Workspace""}"

Power Query

enter image description here

Upvotes: 1

Views: 1347

Answers (1)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60174

The text is not being truncated -- only the display in the lower window is truncated, as noted by the ellipsis at the end.

You can prove this in several ways.

  • LEN(original text) with the leading and trailing double quotes removed; and the doubled-double quotes replaced by single-double quotes will have the same length as Text.Length([Audit Data])
  • Enter a custom column with something like =Text.End([Audit Data],150) and you'll see what you are missing in the preview at the bottom of the page.
  • Split [Audit Data] by a fixed number (eg 1000) and you'll see the entire string in the different columns.
  • Load the data table to an Excel worksheet and you will see that no text has been lost

Upvotes: 2

Related Questions