title: Excel Power Query - Problem with loading of data
body: <p>I am using Power Query to get data from one workbook, 4 worksheets. On each worksheet are approximately 5 thousands row, and I need to merge all 4 worksheets into one table (the table will have the same number of rows as the original ones, but cca 200 columns). I am using power query for the merging but the problem is that the loading is taking very long and although the file has 6mb the power query load over 100mb and sometimes tells me there is not enough memory.</p>
  <p>What could slow down power query?</p>
  <p><strong>Power Query code</strong> <em>(wArrSource is table which loaded 4 worksheets):</em></p>
  <pre><code>let
      Source = wArrSource,
      #&quot;Changed Type&quot; = Table.TransformColumnTypes(Source,{{&quot;Column1&quot;, type date}}),
      #&quot;Merged Table1&quot; = Table.NestedJoin(#&quot;Changed Type&quot;, {&quot;Column2&quot;}, wArrTable1, {&quot;Column2&quot;}, &quot;wArrTable1&quot;, JoinKind.LeftOuter),
      #&quot;Expanded wArrTable1&quot; = Table.ExpandTableColumn(#&quot;Merged Table1&quot;, &quot;wArrTable1&quot;, Different columns}),
      #&quot;Merged Monatswerte&quot; = Table.NestedJoin(#&quot;Expanded wArrTable1&quot;, {&quot;Index&quot;}, wArrPumaMonatswerte, {&quot;Index&quot;}, &quot;wArrPumaMonatswerte&quot;, JoinKind.LeftOuter),
      #&quot;Expanded wArrTable2&quot; = Table.ExpandTableColumn(#&quot;Merged Monatswerte&quot;, &quot;wArrTable2&quot;, {&quot;*A lot of columns here*&quot;}
      #&quot;Merged Queries&quot; = Table.NestedJoin(#&quot;Expanded wArrTable2&quot;, {&quot;Index&quot;}, wArrTable3, {&quot;Index&quot;}, &quot;wArrTable3&quot;, JoinKind.LeftOuter),
      #&quot;Expanded wArrTable3&quot; = Table.ExpandTableColumn(#&quot;Merged Queries&quot;, &quot;wArrTable3&quot;, &quot;*A lot of columns here*&quot;}),
      #&quot;Added Month&quot; = Table.AddColumn(#&quot;Expanded wArrTable3&quot;, &quot;Month&quot;, each Date.Month([ AB Date])),
      #&quot;Added Period&quot; = Table.AddColumn(#&quot;Added Month&quot;, &quot;Period&quot;, each if [Month]&gt;9 then [Month]-9 else [Month]+3),
      #&quot;Added FY&quot; = Table.AddColumn(#&quot;Added Period&quot;, &quot;FY&quot;, each Date.Year([ AB Date])+ (if [Month]&gt;9 then 1 else 0)),
      #&quot;Changed Type1&quot; = Table.TransformColumnTypes(#&quot;Added FY&quot;,{{&quot;Month&quot;, Int64.Type}, {&quot;Period&quot;, Int64.Type}, {&quot;FY&quot;, Int64.Type}, {&quot;Ist 2020&quot;, type number}, {&quot;FC 2020&quot;, type number}, {&quot;FC 2021&quot;, type number}, {&quot;FC 2022&quot;, type number}, {&quot;FC 2023&quot;, type number}}),
      #&quot;Added Sum&quot; = Table.AddColumn(#&quot;Changed Type1&quot;, &quot;Sum&quot;, each [Ist 2020]+[FC 2020]+[FC 2021]+[FC 2022]+[FC 2023]),
      #&quot;Added ID&quot; = Table.AddColumn(#&quot;Added Sum&quot;, &quot;ID&quot;, each Number.ToText([#&quot;Column2&quot;],&quot;G&quot;) &amp; &quot;_&quot; &amp; [Column 5]),
      #&quot;Replaced Errors&quot; = Table.ReplaceErrorValues(#&quot;Added ID&quot;, {{&quot;Period&quot;, 0}, {&quot;FY&quot;, 0}}),
      #&quot;Added Value&quot; = Table.AddColumn(#&quot;Replaced Errors&quot;, &quot;Value&quot;, each if [Status]=&quot;ABC&quot; or [Status]=&quot;BED&quot; then null else &quot;x&quot;),
      #&quot;Added AB2&quot; = Table.AddColumn(#&quot;Added Value&quot;, &quot;AB 2 Date&quot;, each if [wArrTable1.AB 2 IST] &lt;&gt; null then [wArrTable1.AB 2 IST] else if [wArrTable1.AB 2 FC] &lt;&gt; null then [wArrTable1.DI 2 FC] else if [wArrTable1.AB 2 Plan] &lt;&gt; null then [wArrTable1.AB 2 Plan] else &quot;&quot;),
  in
      #&quot;Added AB2&quot;
  </code></pre>