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

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors