Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi,
I have Appended query table (master data) with multiple large CSV files with 1 million= rows, and then I have added merged columns from other tables in the master data table using query editor, the appended master data table is static and does not need refresh however the other tables which are being used to merge with master data table need refresh.
Below is the sample query for master data table:
let
Source = Table.Combine({#"2023", #"2019", #"2020", #"2021"}),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"address", type text}, {"client", type text}, {"segment", type text}, {"city", type text}, {"localid", type text}, {"pincode", Int64.Type}, {"Year", Int64.Type}, {"Country", type text}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"Year", Int64.Type}}),
#"Added Custom3" = Table.AddColumn(#"Changed Type1", "CountryandClient", each [Country]&[clients]),
#"Merged Queries1" = Table.NestedJoin(#"Added Custom3", {"CountryandClients"}, #"Unique Clients (product level)", {"Con- Country&clients"}, "Group", JoinKind.LeftOuter),
#"Expanded Group" = Table.ExpandTableColumn(#"Merged Queries1", "Group", {"Group"}, {"Group.Group"}),
#"Added Conditional Column1" = Table.AddColumn(#"Expanded Peers", "Custom", each if [Table1.client] <> null then [Table1.client] else if [Group.Group] <> null then "Group" else null),
in
#"Added Conditional Column1"
The problem is, If I refresh the master data table in order to reflect the updated data, it starts with first appending the CSV files, which is not required since this is a static data and then it runs other queries to update the merged columns. The appending actually takes a huge amount of time and is not required.
Is there any way I can stop this appending.
Since I am new to power bi, I am not aware of alternatives, hence your help is greatly appreciated. Thanks.
Please see this article/video to understand how Power Query works. To mitigate it, you can consider the 2nd link to leverage incremental refresh when combining your files.
Why Does Power BI Query My Data Source More Than Once? - Chris Webb's BI Blog (crossjoin.co.uk)
https://www.youtube.com/watch?v=IVMdg16yBKE
Pat
User | Count |
---|---|
84 | |
80 | |
72 | |
71 | |
55 |
User | Count |
---|---|
108 | |
106 | |
93 | |
85 | |
67 |