cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper III
Helper III

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?

Capture.JPG

5 REPLIES 5
Super User IV
Super User IV

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Super User IV
Super User IV

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 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


@mahoneypat 

Here is the oneDrive file:

OneDrive 

 

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)

Super User IV
Super User IV

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


@mahoneypat 

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:

https://community.powerbi.com/t5/Power-Query/Is-there-a-way-to-enter-a-Text-String-by-itself-in-Tabl... 

 

 

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?

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors