How To Break Out Large Tables Into Multiple Tables And Build A Model - Power BI Tips
HTML-код
- Опубликовано: 11 сен 2024
- How do you clean up a huge flat database file from SQL server into Power BI? This is a common question in the Enterprise DNA Support Forum and I want to do a quick deep dive into this simple process.
I will show you how to manage a huge flat file by breaking large tables into multiple tables and create a structured model.
You must understand that this process cannot be done on the front-end but in the Power Query Editor.
**** Video Details ****
1:34 - transforming the data using Power BI Query Editor
3:15 - changing a column into a lookup table
4:00 - create abbreviations and index
6:17 - removing column and putting related columns together
**** Learning Power BI? ****
FREE COURSE - Ultimate Beginners Guide To Power BI - portal.enterpri...
FREE COURSE - Ultimate Beginners Guide To DAX - portal.enterpri...
FREE - 60 Page DAX Reference Guide Download - enterprisedna....
FREE - Power BI Resources - enterprisedna.c...
Enterprise DNA Membership - enterprisedna....
Enterprise DNA Online - portal.enterpri...
Enterprise DNA Events - enterprisedna....
****Related Links****
Creating Dynamic Ranking Tables Using RANKX In Power BI - blog.enterpris...
Master Virtual Tables in Power BI Using DAX - blog.enterpris...
Creating Power BI Tables By Using UNION & ROW Function - blog.enterpris...
****Related Course Modules****
Data Visualization Tips - portal.enterpr...
Advanced DAX Combinations - portal.enterpr...
Advanced Data Transformations & Modeling - portal.enterpr...
****Related Forum Posts****
How To Create A New Table From Many Other Similar Tables - forum.enterpri...
How To Break Out Large Tables Into Multiple Tables And Build A Model - Power BI Tips - forum.enterpri...
Creating A New Table From Multiple Other Tables With Similar Data - forum.enterpri...
For more power BI table queries to review see here - forum.enterpri...
*****Related Links*****
Creating Dynamic Ranking Tables Using RANKX In Power BI - blog.enterprisedna.co/dynamic...
Master Virtual Tables in Power BI Using DAX - blog.enterprisedna.co/working...
Creating Power BI Tables By Using UNION & ROW Function - blog.enterprisedna.co/creatin...
*****Related Course Modules*****
Data Visualization Tips - portal.enterprisedna.co/p/dat...
Advanced DAX Combinations - portal.enterprisedna.co/p/adv...
Advanced Data Transformations & Modeling - portal.enterprisedna.co/p/adv...
*****Related Forum Posts*****
How To Create A New Table From Many Other Similar Tables - forum.enterprisedna.co/t/how-...
How To Break Out Large Tables Into Multiple Tables And Build A Model - Power BI Tips - forum.enterprisedna.co/t/how-...
Creating A New Table From Multiple Other Tables With Similar Data - forum.enterprisedna.co/t/crea...
For more power BI table queries to review see here - forum.enterprisedna.co/search...
Exactly what I needed. Searched far and wide and finally stumbled upon a solution to my problem. Nice and clear, thanks man
Hi Keifer, glad that this video is of help! If you haven't yet, you can subscribe to our channel to see all our upcoming Power BI video tutorials and announcements. Cheers!
I rarely leave comments, but have to admit that u are my savior for today.
Got my course work deadline coming, and got stuck at how to create tables out of existing one.
Clear, quick, simple.
Thanks again, happy upcoming new year!
Keep it up!
Hi Max, glad that this video is of help! If you haven't yet, you can subscribe to our channel to see all our upcoming Power BI video tutorials. Cheers!
THANK YOU this has made my job even that much easier... can't wait to see how much I can do with my data now... this ROCKS!!
Very informative video, thank you very much!
Thanks, this is exactly what i was looking for.
thank you for your perfect content, good luck
Hi Ali, glad that you appreciated our content! If you haven't yet, you can subscribe to our channel to see all our upcoming Power BI video tutorials. Cheers!
Not sure using Reference is appropriate here. Following your logic you'll probably decide to lose the original column from your fact table before loading. Now if you do that it'll cascade to your new query as you used reference and you'll be missing your starting column... Also I think the PowerBi engine does all that comlression behind the seen so I'm not convinced you'll save on space using sub tables so it'll just help structure the data but not specifically save on model size.
HI Thibault: Great comment. What would you recommend ? How would you normalize this model? Would you use the duplicate feature VS the reference feature? I am facing this dilemma myself today at work since I have a huge flat file with 187 columns and only 1200 lines and I want to normalize this flat table to better analyze the data. Regards
@@jazzista1967 two options. Using duplicate as you suggested or untick "enable load" for the main query and create sub queries referencing it that you load. One of your sub query would have all the facts and all other would be dimensions ideally.
Thibault . Thank you so much for your explanation.
So your huge flat file table should function as your data table, and you create multiple reference look up tables from it, then you can trim down your flat file table to only the necessary data fields? How do you create relationships?
That was a very good video, thank you.
Hi Surviving Adventures, glad you appreciated the video. You can subscribe to our channel to see all our upcoming Power BI video tutorials. Here’s the link: ruclips.net/channel/UCy2rBgj4M1tzK-urTZ28zcA
So what would the process be to replace the text column in the facts table with a foreign key (index)?
Yes that will be helpful. How do you join this newly normalized table to the fact table. Obviously we should remove that column from the fact and replace it with some number column but how do you do that right?
@@aamminikutty Same question over here. Would love to get the complet story solution
Hes missed out the complicated part and shown the easy parts
@@amarkhaliq641
You might have the answer already but for those just setting out:
Create a calculated column using the new index column with unique values as your parameters to assign values for each row(for a particular column with relevant values) in your fact file. Hope this helps.
I have a table of accounts (from Dynamics 365) that is a mix of customers and suppliers. Could I use this technique or a similar one to split that table into two (one for each account type)?
yes
What is difference between Duplicate & reference table ?
Hello Balaji M,
Thank You for posting your query onto our channel.
The main differences between the "Duplicate Table" and the "Reference Table" is as mentioned below -
1. In simple terms, Duplicate will duplicate the code of the query while Query Reference will only refer the result of the query.
2. Duplicate is generally used when you would like to create a similar query and you do not want to type the same code. You can make changes to this query.
3. Reference in Query means you would like to use that Query results in some other queries where your original query remains as a base Query and it can be used in other queries for the further processing.
4. Changes made in the source query will not be reflected in the Duplicate table but will automatically get reflected in the Referenced table.
5. To explain the above points you can check the M Code generated under the Advanced Editor for both i.e. the Duplicate as well as Reference table.
For example, you want to create a duplicate table for the Customers. The M Code generated for the Duplicate table will be as follows -
let
Source = Excel.Workbook(File.Contents("D:\DOCUMENTS\Enterprise DNA\Financial Reporting Resources\Financial Reporting resource pack\Financial Dataset - Harsh.xlsx"), null, true),
Customer_Data_Table = Source{[Item="Customer_Data",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Customer_Data_Table,{{"Customer Index", Int64.Type}, {"Customer Names", type text}})
in
#"Changed Type"
Whereas when you create a reference table for the Customers. The M Code will be generated as follows -
let
Source = Customers
in
Source
And this is the reason why whatever changes we make in the original query will by default get reflected in the Reference table but not in the Duplicate table.
Hoping you find this explanation useful and helps you to understand the difference the "Duplicate Table" and the "Reference Table".🙂
Thanks and Warm Regards,
Enterprise DNA
@@EnterpriseDNA Thanks for your time on explanation.
Thanks for sharing. I tried this and found that my reference tables also send queries to my database, which causes the large table being loaded multiple times. Is that possible to have a “table” using the large table loaded already but with additional filter to get a subset of the large table?
You can load once in power query the fact table and then use DAX to create calculated tables
Hi Min Gao
In case Power Query is loading Data multiple times even for reference tables, suggest to create inside Power BI using DAX queries like Filter(), SUMMARIZE(), SELECTCOLUMNS() etc.
Hi Sam:
Thanks for sharing the video.
In my organization we have a fact table with more than 2billions of rows. And we are using manage aggregations to show aggregated data on numeric values. And if we have to drill down to customer we are using direct query to load our fact table.
My question to you is I want to create slicers from certain colums of fact table which are in group by clause in aggregations so that they can slice both aggregated tables and fact table.
If I create slicers from fact table they take huge amount of time to load some values. How to achieve this??
Hi Space Tech, thanks for posting your comment here in the video. This topic has been discussed several times in Enterprise DNA Forum. You might get the answers you are looking for. If you want to check it out here is the link: forum.enterprisedna.co/search?q=create%20slicers%20from%20certain%20colums%20of%20fact%20table%20which%20are%20in%20group
@@EnterpriseDNA
Hi Sam:
First of all I would like to thank you, because of all your guidance and videos I have cleared DA-100 exam today.
I can not find anything in the link that you provided.
Were you able to solve this issue?
I need a link to download this sales dataset please
Hi factsbymargret, thanks for posting your comment in the video. All pbix file/datasets are available for download in the Enterprise DNA Online, which is accessible via Membership. Check out the link below. Thanks!
portal.enterprisedna.co/
can we loop the dax query
Hello Abhijeet Bakale,
Thank you for posting your query onto our channel. We really appreciate you taking your valuable time to post it.
Well, your query is not understandable to us here. And therefore, we request you to please write back to us by elaborating your query in a proper and specified manner so that we can assist you in an efficient manner.
For furthermore queries, you can also reach out to us onto our Community Forum where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference as well.
Hoping you find this useful. Subscribe to our RUclips channel so that you don't miss out on any updates pertaining to the Power BI. You can also join our Power BI group on LinkedIn to receive latest updates on Power BI.
Cheers,
Enterprise DNA
forum.enterprisedna.co/
ruclips.net/channel/UCy2rBgj4M1tzK-urTZ28zcA
www.linkedin.com/groups/12004506/
Hey bro! Do you have a link of this dataset?
Hi Mike,
Thanks for posting your query here on the channel.
The dataset is available in the Enteprise DNA Online, which is accessible via membership.
portal.enterprisedna.co/
I use a Table.Distinct( Table.FromValue( Table[Column] ) ) to get unique values from a column in another query. This is more efficient then the reference then remove columns.
And when you create a referenced table and delete the column you use from the source to create the lookup table, that referenced get broken and doesn’t work anymore. Or am I missing something?
That looks nice. What would the code be you would have two columns in the main table (code and description)
Now how can I sum the amount of each channel here?
Hello DakavesKaves,
thank you for posting your query onto our channel.
Now just create a simple measure as shown below -
Total Sales = SUMX( Sales , Sales[Quantity] * Sales[Price] )
Once you create the measure just drag the field "Channel" and a measure "Total Sales" and you'll have the bifurcation of amounts channel wise.
For furthermore queries, you can also reach out to us onto our Community Forum where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference.
Hoping you find this useful and meets your requirements that you've been looking for.
Happy Learning!!!
forum.enterprisedna.co/
@@EnterpriseDNA Thank you, this worked!