Create a data set by extracting data from a table? - Part 2. Partial data. DataModel. DAX.

To clarify, analysis, slicing/dicing of data is best done in DAX.

However, if you are doing statistical analysis on returned table using some other tool. PQ is still the solution

One clarification is needed. For each data set, should it include the specified weekday for each week? Resulting in some week's being skipped.
I.E. For Dataset 1. Monday only for each week returned.

If you can mock up manually created sample of each type (with about 5 rows) using same source data. It would make it easier to help.

Originally Posted by CK76

To clarify, analysis, slicing/dicing of data is best done in DAX.

However, if you are doing statistical analysis on returned table using some other tool. PQ is still the solution

Hi, CK76,

Many thanks for your continued assistance.

Yes, in the end, I want this dataset as a simple table in Excel like the one you created for me yesterday.

I have an auto-filter macro which lets me filter dates/weeks from say dataset 2 in dataset 5. This will be my approach in order to perform analysis, i.e., filter a partial week dataset and find the result of the final completed week to see if I can find anything interesting.

One clarification is needed. For each data set, should it include the specified weekday for each week? Resulting in some week's being skipped.
I.E. For Dataset 1. Monday only for each week returned.

Important question, yes.

Certainly, several weeks should/will be skipped when looking at Dataset 1 (Monday only) if there is no Monday in a particular week.

When looking at Dataset 2 - I envision excluding all weeks which don't have a Tuesday.

When looking at dataset 3 - I envision excluding all weeks which don't have a Wednesday.

Etc.

In summary, I think the default option would be to skip all weeks on data set 1 without Mondays, on data set 2 without Tuesdays, ..., on data set 4 without Thursdays.

Another important distinction may be to add a column which tells what the first day of a given week is. That way, I can have the option to exclude weeks that don't start on a Monday when filtering.

If you can mock up manually created sample of each type (with about 5 rows) using same source data. It would make it easier to help.

Certainly!

But it will likely take me some time, so I will not be able to produce anything until later tonight at best. Busy helping out my parents at the moment.

Best regards,

Elijah

I mean, how should data be structured?

I.E. Weekday Low, Weekday High needed for each along with all the other logic replicated exactly same as dataset 5?

Hmm, let me think on the best process. It's probably best to create custom function using "M" to do filter operation on the SeriesTable.
I'll probably have something this afternoon.

Here you go. I've converted the pasted query into function.

Please Login or Register  to view this content.

It will accept reference to original query as first argument, and Weekday index# (0 = Sun, 1 = Mon etc) as second argument.

Original query has been altered to just add 2 Custom columns. "Year" & "WkdayNum". "WkdayNum" is used for filtering purpose using 2nd argument from above function.
Original query is there as reference point, and left as connection only.

Please Login or Register  to view this content.

To generate each data set. You'd invoke custom function.
For dataset 1:
0.JPG

Just change daynum argument each time function is invoked (1 to 5). Each time it's invoked it will generate table as result.

See attached sample.

As for your questions...

1. It would not be a problem. Queries and function is constructed in a way it will ignore any column outside of the ones used in current query (it will be in SeriesTable query, but not used else where).

2. You can see how it's handled in step 7 & 8 of previous thread. Basically earliest date to have High/Low value in a given week is retained.

7. Select all but the "Date" column and Group By. Use "LowDate" as column name, Operation = "Min", on "Date" column. This will eliminate duplicated row and return min date for low.
8. Repeat step 5 to 7, but this time using "High" column instead of "Low". (I left it as Min for "Date", but you can change it to Max to get last high date in week).

Administrative Note:

Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

For normal conversational replies, try using the QUICK REPLY box below.

Hi again, CK76,

So, I was able to invoke the custom function and generate the datasets from scratch. Nice!

Further, I was able to:

1) Copy and paste the original query SeriesTable to my SourceWorkbook (the one I intend on using)

2) Copy and paste the custom function from your workbook to the SourceWorkbook

3) Generate datasets from scratch using your custom function.

Great!

-

I have however some minor problems which needs to be adressed.

1) In my original SourceWorkbook - columns "Week" and "PriorClose" are not included in the table. In the workbook supplied here initially, Week and PriorClose were calculated by me using basic Excel formulas.

Since the original SourceWorkbook is populated with data from an external database using VBA - I'm unable to add them in manually (they disappear when I load data).

I see two solutions:

a. Create an additional linked sheet where I pull out data similar to the "Daily Data" sheet I supplied here first. However, this will give the table a new name (SeriesTable4), so I will probably need to adress that somehow in the query..

b. Add in "Week" and "Prior Close" in PowerQuery in the original query. Seeing the complexity of this function/query right now, that's certainly way beyond what I'm capable of on my own.

Thanks again for any further assistance on this.

Best,

Elijah

EDIT:

I also took the liberty of adding in a few columns myself and used Excel formulas to handle this directly in the Excel sheet. I did not dare to touch the function/query in PowerBI, but I imagine this should work just as well.

2.PNG

Also, while playing around with the data this morning, I realized I lack some data to calculate another metric I'm interested in plotting.

I think it's interesting to see which comes first:

The Week Low or the Week High.

If the Low comes first, I'd label it LowHigh. If the High comes first, I'd label it HighLow.

I imagined creating this myself manually later on using a simple IF function, i.e., if Week Low < WeekHigh; LowHigh; HighLow.

May have to convert the weekday into numbers to make that calculation though.

However, I now realized this will be difficult if the Week High/Low comes on the same day. But perhaps I should just add that as a special case labeled "Equal" and not bother with it.

Alternatively, I have the timing of the Low/High for each day in my original data set, so that could be extracted in order to differentiate. But it probably involves quite a few extra steps in the queries, so I'm leaning on just dropping that altogether.

1.PNG

Supply sample of your raw data, and mock up of your desired result here in the thread please. It's so much easier to help with that info.

Originally Posted by CK76

Supply sample of your raw data, and mock up of your desired result here in the thread please. It's so much easier to help with that info.

Sure.

Here goes.

The sheet "Instrument" contains my raw data containing Date, Day and O (Open), H (High), L (Low), C (Close). To anonymize my data, I've blanked out headings and all other values. This data is auto-populated/rebuilt every time I load data, so I can't make any changes to it.

I created a mock-up of the desired result in the sheet Monday - Friday.

3.PNG

It's not really any different from what you already created for me. I renamed some of the columns, moved a few of them and added in "Month".

The difference now is that the raw data from the original query only contains Date, Weekday and O, H, L, C. So, Prior Close (basically the Close (C) of the prior day/row) needs to be calculated and also Week.

- - -
- - -

Finally, there's the matter of timings mentioned in the prior post. I just checked and there's only 10 days where the weekday high/low happens on the same day. So, unless you really feel like it, I don't think it's worth the trouble. I added in those columns at the end just in case, but I don't think it's worth the trouble and probably is 'useless' information.
But if you should, it's as simple as associating the Week Low with the DayLow time value as can be found in column Z. Conversely for the Week High and the DayHigh value in column AA.

Regardless of the above, on the column Order, I would want to calculate three possible values using an IF function:

If Week Low = Week High: "Equal" (if timings are implemented, this disappears)

If Week Low < Week High: "LowHigh"

if Week Low > Week High: "HighLow"

Basically, I'm creating a metric which allows me to filter if the Week Low comes before or after the Week High. I have not looked into this yet, but it's possible I need to convert Weekdays into numbers in order to perform this test. But this is something I can handle on my own for sure.

Best regards,

Elijah

Hmm, there's one major issue with your set up. Loading that many columns into PQ is going to be very expensive.

When tested on my machine, Memory usage spiked to above 8Gb. That's abnormally high for dataset this size.

Also, speed of query suffers to almost unbearable point. Took about 5 min to load back data with just 3 additional custom columns with unnecessary columns removed...

Let me dissect your workbook and see if there's ways to speed up things.

Don't worry about it. I think I solved it by using Named range to load PQ. Took about 10 sec.

Formula:

Please Login or Register  to view this content.

Then query it...

Please Login or Register  to view this content.

Ok, I think all the quirks have been dealt with and is working.

Issues/quirks dealt with.
1. Your original sample was based on English system, but this one is based on Norwegian. So default week start date was different and had to be adjusted.
- Dealt with by specifying "en-US" for Date.DayOfWeekName's second argument, and by adding Day.Monday / Day.Sunday for Date.WeekOfYear & Date.DayOfWeek functions.

2. Too many columns for PQ to handle (at least on my 16GB RAM machine) over 255 columns...
- Added Named Range holding specific area of table needed for query.
In Formula ribbon tool -> Name Manager. Named it "PQRange" and used formula in previous post.
- Then replaced table name with Named Range name as source reference in PQ.

3. Added 0 based index column to the table. In order to access Prior Close value.

Please Login or Register  to view this content.

Note that this only works if data is sorted on [Date] column, from latest to oldest.

See attached.
I have removed most of the unnecessary columns in sheet, to reduce file size. And removed broken named ranges. But that has no impact on query code.
I only did sample invoke function for Dataset 5. But it works for all.

Wait, on which workbook are you running your code?
You should only do it in your actual workbook. Not the sample I provided. As there were many broken links etc. I had cleaned those up.

Then you'll need to add named range to your workbook using the formula provided.

Then go into advanced editor and copy M codes there. Then paste it into blank query in your workbook.

Step 2 - It is done on the reference query (i.e. not the function or the invoked)

Step 3 - You can just follow the steps applied in my sample. It's the same table where step2 is applied.

Step 4 - You can paste both, separately into it's own query. However, you must have named range already present in your workbook (If you go this route, no need to change query in Step 2 or 3. It's already there).

Hi again, CK76,

Understand. I'd prefer to just skip to step 4 then.

The problem now seems to be that the named range PQRange is not found by PowerQuery. Actually, I get the same error message when opening your "Demo" workbook. Could there be some conversion issues with formulas from English to Norwegian Power Query?

These tend to auto-correct on normal formulas. I did check a few on the translation tool online, but didn't find anything.

Or am I messing up elsewhere?

Thanks in advance for any pointers.

1.PNG

That could be it. Meaning you have to translate INDIRECT() to Norwegian equivalent.

Here's translation site.
https://www.excel-function-translati...norwegian.html

INDIREKTE()

Originally Posted by CK76

That could be it. Meaning you have to translate INDIRECT() to Norwegian equivalent.

Yes, it seems like the error lies there, but I already translated it to INDIREKTE.

However, upon closer inspection, there's more syntax mismatch between the English and Norwegian formula which is not adressed in the translator I typically use:

http://en.excel-translator.de/translator/

I think your

Please Login or Register  to view this content.

should translate to

Please Login or Register  to view this content.

Note that I changed from "," to ";" and from "#All" to "#Alle". Also, I removed the "".

Still, Excel (Power Query) won't recognize that range. So not sure what's up.

If I could bother you, one easy way may be that you copy the correct formula in English into a blank cell in a workbook. I open this workbook and copy/paste that formula. Excel should translate it directly then.

But, the formula should be correct. So not sure what's up.

Could it be the Power Query formula which reference the PQRange which doesn't translate correctly as well?

(not able to paste it without getting a firewall message for some reaosn)

Your table reference syntax is off.

You can get the syntax by selecting the table range (including header) from [Instrument]:[C] columns (during cell edit mode in some cell).

Not sure about PQ language difference. You can always query some small table. Then replace the name of that table with Named Range.

EDIT: Oh, and to use INDIREKTE() you'll need to nest table reference in double quotes.

So when you do...

Please Login or Register  to view this content.

It doesn't recognize it?

Other possibility is that range reference is still off. When you enter following formula in some cell what do you get (note: you should translate it)?

Formula:

Please Login or Register  to view this content.

If you get error value. Then it means the formula for the named range evaluates to error and causing PQ to return error.

Hmm, not sure. Worked fine on my end without issue.

0.JPG


You could always use VBA to paste range to some other sheet and then convert it to table and query that instead of using Named Range.

Edit: One last thing to check is that Scope of the Named Range is set to Workbook (and not Worksheet).

is it okay to add columns to PQ tables?

To be honest I'm not sure. I never add columns in sheets to query table. I almost always, load data set to data model once desired transformation is done in PQ. Then use DAX measures for calculation and load to PowerView/PowerPivot.

If you are not having issue. I think it should be fine, as long as the number of columns returned from PQ does not change.