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

[Elijah]

Hi all,

I received fantastic help from CK76 to solve my initial problem of extracting/building a weekly dataset from a dataset consisting of daily data in this thread:

Create a data set by extracting data from another data set?


This weekly data consist of the entire week minus Saturday/Sunday. To make matters worse - some days have only 3-4 weeks due to holidays and such. But that was handled.

For statistical analysis, I'm now interested in creating additional datasets which consists of weekly data as the week progresses:

Dataset 1 - Monday

Dataset 2 - Monday - Tuesday

Dataset 3 - Monday - Wednesday

Dataset 4 - Monday - Thursday

Dataset 5 - Monday - Friday: This would be the dataset already created in the prior thread.

According to CK76, this particular problem is best solved using Data Model and DAX. Sadly, I don't have any experience using these tools.

Any pointers on how to proceed?

I realize that this is a task that may be beyond what can be asked for free in a forum. If so - maybe it's possible that I could later post this in the commercial part of the forum, but I would need to buy credits for that and I'm not sure what the price of such a job would be.

Thanks in advance for any help.

Best regards,

Elijah

[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

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.

[Elijah]

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

[Elijah]

I imagine to set it up like this with one sheet per dataset:

1.PNG

[CK76]

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?

[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?

I think so, yes.

I'm wondering also if there should be added a column (s) which tells me what the first day of the week is and the last day of the week is. But that should be easily created by using the StartDate/EndDate columns in PQ or Excel itself.

Otherwise I imagine it to be the same structure, yes.

[CK76]

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.

[Elijah]

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.

Fantastic.

If I may, there are two things that could be mentioned:

1. My main workbook/table is set up slightly different than the one you've used. I didn't upload the entire thing because it contains a lot of superfluous columns and because of privacy issues. But the columns in use are the same ones.

I would love to set up this query using my original workbook. Is it possible to just copy a query from one workbook to the other then?

2. For maximum/minimum values on the day of week high/low values - how were duplicate values adressed? In my mind, the first occurence is the correct one. Meaning - if the day low is found on Tuesday and later a similar value on Thursday - Tuesday is still the day for the Low. Same with High values.

Thanks again.

[CK76]

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

(tbl as table, daynum as number) =>
let
    Source = tbl,
    #"Filtered Rows" = Table.SelectRows(Source, each [WkdayNum] <= daynum),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Week", "Year"}, {{"StartDate", each List.Min([Date]), type datetime}, {"EndDate", each List.Max([Date]), type datetime}, {"High", each List.Max([High]), type number}, {"Low", each List.Min([Low]), type number}, {"# Days", each Table.RowCount(_), type number}}),
    #"Merged Queries" = Table.NestedJoin(#"Grouped Rows", {"StartDate"}, tbl, {"Date"}, "SeriesTable", JoinKind.LeftOuter),
    #"Expanded SeriesTable" = Table.ExpandTableColumn(#"Merged Queries", "SeriesTable", {"Prior Close", "Open"}, {"Prior Close", "Open"}),
    #"Merged Queries1" = Table.NestedJoin(#"Expanded SeriesTable", {"EndDate"}, tbl, {"Date"}, "SeriesTable", JoinKind.LeftOuter),
    #"Expanded SeriesTable1" = Table.ExpandTableColumn(#"Merged Queries1", "SeriesTable", {"Close"}, {"Close"}),
    #"Merged Queries2" = Table.NestedJoin(#"Expanded SeriesTable1", {"Week", "Year", "Low"}, tbl, {"Week", "Year", "Low"}, "SeriesTable", JoinKind.LeftOuter),
    #"Expanded SeriesTable2" = Table.ExpandTableColumn(#"Merged Queries2", "SeriesTable", {"Date"}, {"Date"}),
    #"Grouped Rows1" = Table.Group(#"Expanded SeriesTable2", {"Week", "Year", "StartDate", "EndDate", "High", "Low", "# Days", "Prior Close", "Open", "Close"}, {{"LowDate", each List.Min([Date]), type datetime}}),
    #"Merged Queries3" = Table.NestedJoin(#"Grouped Rows1", {"Week", "Year", "High"}, tbl, {"Week", "Year", "High"}, "SeriesTable", JoinKind.LeftOuter),
    #"Expanded SeriesTable3" = Table.ExpandTableColumn(#"Merged Queries3", "SeriesTable", {"Date"}, {"Date"}),
    #"Grouped Rows2" = Table.Group(#"Expanded SeriesTable3", {"Week", "Year", "StartDate", "EndDate", "High", "Low", "# Days", "Prior Close", "Open", "Close", "LowDate"}, {{"HighDate", each List.Min([Date]), type datetime}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows2", "Weekday Low", each Date.DayOfWeekName([LowDate])),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Weekday High", each Date.DayOfWeekName([HighDate])),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom2",{{"Weekday Low", type text}, {"Weekday High", type text}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Changed Type1",{"StartDate", "EndDate", "# Days", "Week", "Prior Close", "Open", "High", "Low", "Close", "Weekday Low", "Weekday High", "Year", "LowDate", "HighDate"}),
    #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"Year", "LowDate", "HighDate"})
in
    #"Removed Columns"

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.

let
    Source = Excel.CurrentWorkbook(){[Name="SeriesTable"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Year", each Date.Year([Date])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "WkdayNum", each Date.DayOfWeek([Date])),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom1",{{"Date", type datetime}, {"Week", Int64.Type}, {"Day", type text}, {"Prior Close", type number}, {"Open", type number}, {"High", type number}, {"Low", type number}, {"Close", type number}, {"Year", Int64.Type}, {"WkdayNum", Int64.Type}})
in
    #"Changed Type"

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.

[Elijah]

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

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

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.

Wow. This is amazing. Thank you so much for this!

I have to log off for 30 minutes, but hope to be back in a little while. If not tonight, I'm back early tomorrow.

First impression is that implementing/understanding what you've done is way, way above my head. I don't even know how to invoke a custom function (just tried very briefly, but it was not quite correct).

I'd definitely want to implement this in my original source workbook, so I hope I'll be able to do that.

Many thanks again and have a nice evening!

Best regards,

Elijah

[CK76]

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).

[AliGW]

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.

[Elijah]

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

[Elijah]

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

[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.

[Elijah]

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

[CK76]

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.

[Elijah]

Hi CK76,

Are you loading anything more than the necessary columns from the instrument sheet?

A solution could be to simply create a linked sheet which extracts only the necessary columns and then use that sheet for Power Query? A sheet containing the same values as the workbook initially supplied?

On my cell phone now. If not, I would have created a suggestion.

EDIT: Can do that tomorrow

[CK76]

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

PQRange =INDIRECT("SeriesTable[[#All],[Instrument]:[C]]")

Then query it...

= Excel.CurrentWorkbook(){[Name="PQRange"]}[Content]

[Elijah]

Hi, CK76,

Many thanks.

But where do I apply these formulas may I ask?

Best regards,

Elijah

[CK76]

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.

#"PreviousStepName"{[Index]+1}[C]

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.

[Elijah]

Hi, CK76,

Thank you so much for this!

Sadly, I'm getting an error when I try to load data into my sheet Instrument using the VBA.

1.PNG

2.PNG

You mentioned naming ranges - could this be why?

Also, I tried to run the function fncDataReshape now - but I'm not able to select a "tbl" in the dialog box.

Any ideas?

Thanks.

Elijah

[CK76]

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.

[Elijah]

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.

Aha. That explains it!

Okay. I'll try that in an hour or so and see if I can pull it off. Will report back.

Best,

Elijah

[Elijah]

Will try again tomorrow. Got stuck tonight.

Pulled off the Named Range allright (I have brief experience with that stuff), I think, but struggle with the Power Query stuff. As you can tell, I'm very inexperienced with PQ.

[Elijah]

Okay, so I tried again today, but I'm stuck...

Step 1 – Add named range
Had to update formula to Norwegian and swap «,» with «;». (semicolon in Norwegian Excel instead of comma in English. Usually, workbooks adapt formulas, but I see that here that didn’t happen on the named range formula)

1.PNG

Step 2 – Then replaced table name with Named Range name as source reference in PQ.

Is this referring to what you said here?

Then query it...

= Excel.CurrentWorkbook(){[Name="PQRange"]}[Content]

And where is this done?

In the older SeriesTable query from the prior workbook?

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

This one, I can manage, but need to first establish the query where I do it, hence step 2.

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

Would that be two codes? Or are you simply referring to the fncDataReshape only?

4.PNG

5.PNG

Thanks again.

Elijah

[CK76]

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).

[Elijah]

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

[CK76]

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()

[Elijah]

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

=INDIRECT ("SeriesTable[[#All],[Instrument]:[C]]")

should translate to

=INDIREKTE(SeriesTable[#Alle];SeriesTable[[Instrument]:[C]])

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)

[CK76]

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.

[Elijah]

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).

Corrected.

=INDIREKTE("SeriesTable[[#Alle];[Instrument]:[C]]")

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

I did that just now. The syntax appears to be precisely the same.

= Excel.CurrentWorkbook(){[Name="Tabell4"]}[Content]

"Tabell4" = "PQRange"

But no go...

Seems like Excel/PQ won't recognize PQRange.

[CK76]

So when you do...

= Excel.CurrentWorkbook(){[Name="PQRange"]}[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)?

=INDEX(PQRange,1, 1)

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

[Elijah]

So when you do...

= Excel.CurrentWorkbook(){[Name="PQRange"]}[Content]

It doesn't recognize it?

Correct.

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)?

=INDEX(PQRange,1, 1)

I suspected so.

But the INDEX formula actually returns value "Instrument" and immediately recognized PQRange as an option. So seems like the range reference is correct after all?

[CK76]

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).

[Elijah]

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

Embarassing!

I was aware of this option and had it set to Workbook earlier, but inbetween actually fixing the correct formula for the named range, it have slipped back to Worksheet.

The query works just fine now! I was just able to extract data for Monday only.

Going to go over the rest now and create the other datasets.

Once again, THANK YOU SO MUCH for all help in this thread! And thank you for being so patient always.

PS: Generally - is it okay to add columns to PQ tables? I will want to perform additional calculations on these tables. Personally, I prefer to do so directly in Excel. This means extra columns.
In the past I've done this without any problems on other sheets and it seems to work fine, but figured I could ask a real professional.

Best regards,

Elijah

[CK76]

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.

[Elijah]

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.

Thanks, CK76.

I think I'll just take my chances. It does seem to work fine so far with other tables where I've done this.

Best regards,

Elijah