Forum Discussion
Preview.Error: The current preview value is too complex to display. Details:
I've seen older posts asking the user to update their application version. However, I feel that I have the newest version:
2.78.5740.481 64-bit
= Table.AddColumn(Source, "Custom", each Query1(Source{[Item="Sheet1",Kind="Sheet"]}[Data]))
Query1 is:
= (YourWorkSheet as table ) as text=>
let
SheetCellA4 =YourWorkSheet[Column1]{3},
SplitByFrom = Text.Split(SheetCellA4, "'From: "){1},
SplitByTime = Text.Split(SplitByFrom, "`"){0},
GetTimeFrame = Text.Start(SplitByTime, Text.Length(SplitByTime)-1)
in
GetTimeFrame
Any ideas on how to fix this error?
5 Replies
Why are you referencing a single table (on a row/column) in a Table.AddColumn function? Why not just write
= Table.AddColumn(Source, "Custom", each Query1([Data]))Regards,
Pat
I apologize, I'm still learning Power Query and M code. I'm not sure what you mean by ' referencing a single table (on a row/column) in a Table.AddColumn function'
I attempted to modify the code from a code given to me in another post:
When I click on the Add Column - Custom Column, I only put in:
Query1(Source{[Item= "Report1", Kind="Sheet"]}[Data])And when I click OK, the rest is automatically populated. Should I just add your code in advance editor?
I may be unclear on your scenario. I was assuming you are combining multiple worksheets and have multiple rows with different worksheets in your table. Your reference inside Query1 looks like it would return the same row/column table for all rows. So I suggested you reference the table/worksheet on the same row (i.e., just [Data]). If I've misunderstood, you can ignore my comments. You could upload your pbix to Google Drive or OneDrive and include a link here, if more specific help is desired.
Regards,
Pat
Here is the oneDrive file:
I uploaded two of the 5 workbooks that I will be working off of.
The Workbook is just one worksheet. I want to reference the time period in cell "A4" to add a column.
Once I understand the M code for the 4 Wk Data.csv, I will eventually add 4 more .csv files (each workbook only having one worksheet) to the Power Query.
I know it sounds like I'm complicating this, but each report has to be ran out of the data base separately, so they are saved as separate files.
End Result (in the new column) would change:
Current Weeks : 'From: 2020 PD 06 WK 4 (24) to 2020 PD 09 WK 4 (36)`
to
From: 2020 PD 06 WK 4 (24) to 2020 PD 09 WK 4 (36)
Here is a pbix with one way to do this. Go to the query editor and replace the FolderPath parameter with the folder path to where you csv files reside. This extracts the csv, gets your value of interest, and then uses text between delimiters to keep just the characters you want. From here, you can hit the expand button on the column with the data "Table"s to get aggregate your data.
Please let me know if any questions or if that is not what you were looking for.
Regards,
Pat
