Latest Enterprise DNA Initiatives


Applied Problem Solving with Power Query/M

This is a great course!

I find myself applauding while watching the transformations.

Thank you Melissa for sharing your insights and abilities!

3 Likes

I’m glad you like it!
Thank you for the feedback :blush:

1 Like

I just finished the Extract All Hierarchy Levels, Recursive Transformation video.

Going through this course, I realize how spoiled I am as I am fortunate enough to have a strong SQL background and the ability to shape my data prior to consumption with Power BI.

Ragged hierarchies are the bane of my existence, scratch that, hierarchies in general are the bane of my existence. As far as I know, they are the most flexible way of consolidating information, but they constantly trip me up.

My data would have started with your finished product…a parent with children. I would have insisted on it. Like I said, I’m spoiled :slight_smile:

I’m thankful I watched this video after Rick de Groot’s Business Intelligence Summit video as the techniques he used (the underscore, functions, etc) gave me a leg up in understanding where you were going with your solution.

I always tell my team to NEVER TRUST THE DATA. This demo reinforces my mantra as it future-proofs the ETL against changes to the source.

Wonderful!

Thank you!

2 Likes

I’m now watching the Convert a PDF to a Tabular Format video which reminded me of consuming a zip file.

Typically, I use a UNIX shell script to execute a PERL script which in turn executes multiple SQL scripts and spool them out to individual text files. After all the SQL scripts are complete, the shell script then ZIPs the resulting text files and FTPs them to my network server and emails me that the files are available.

I ZIP the files because my “fact” tables are typically in the 500MB+ range and I’m trying to be considerate of our network resources.

I then manually unzip the files to my model’s required location.

I’ve used an “M” script that consumes ZIP files and have tried it with some small files (relatively speaking). See < [https://community.powerbi.com/t5/Community-Blog/Working-With-Zip-Files-in-Power-Query/ba-p/1190186]>

Have you tried this?

If so, are there any issues you ran into?

Once again, thanks for this course. I’m finding it EXTREMELY valuable.

1 Like

Also, the comment about filling down in regards to nulls vs blanks was very helpful.

That’s going to save me some heartache in the future. :+1:

2 Likes

Last question regarding Convert a PDF to a Tabular Format…

To me, you treat some of the transformations as steps towards the end solution. Maybe I’m misunderstanding.

How is the overall model size impacted by those steps?

Finally…SO CLEVER!!! It was a joy to watch!

3 Likes

Hi @kaDargo,

Thank you again so much for your feedback, I appreciate that!

Up to now I’ve never worked with zipped files…
.

:wink: :+1:

Not sure I’m understanding your question but model size is only impacted by what you ‘load’ into the model. Any staging doesn’t impact size but can impact performance. However if performance is not an issue, smile and be happy! Optimization can be really trying and time consuming…

1 Like

Sorry, it was a poorly asked question.

What I meant to ask is how is refresh time impacted by the number of steps?

Fewer steps = shorter refresh time? Or, is refresh time mostly impacted by the amount of data your transforming?

The number of steps alone is no indicator of query performance. What is generally speaking more important, the data source (does it allow folding? for example) and/or the types of transformations.

1 Like

Hi @kaDargo, has Melissa answered all of your concerns? If not, how far did you get and what kind of help you need further? If yes, kindly mark this thread as solved. Thanks