Forum Discussion
Change Google Sheet Source to Excel Source
The Power Query Source Step below queries a google sheet xls. How do I change the Source Step to a local folder (same file) on my computer, ex ("C:\Users\Downloads\myfile.xls"), and keep & retain all the transformation steps under it. Code is below
= Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("7ZfNbtswDIBfxcjZB0mWLGm3IMmwFK3tOQ6KoeihQwPstEOGHvr2E+k/0skhVVJgwAwkShjrIxmSos2np8UijXo9p4h+K/f1/Y+kqsu7zarZlsUu/LhrlnUTPjfFOqz7sgpr3WzC2pRb+L1+bLVcQFdIr+7gyqoIy/r7iROPh9ffhz+vL+9p8vXw8/j2cnxPlEsTJZQOl6X6IkR4JcsHkJig8rA6FRZlw5K3im+ukDg7JZguD/szpAVslAMewxCTjMio4MIbPEXHR3c/DPTG1uXDZtdsV8m2qPbNFNZMAKdNp862aq7EiSPOp8aZKWSoYLM+aq57w/ZIjgec7c9ZiWQA5JAmlfGERUG90W3RbOpiCcdoeT+Ej2mx7B+BHg1xk2P8bqKEOKVN6qyaUo6lEWItYVH4TXRpELEkTwQjPKvq/oCiirGSP0705pqy6aOWTFkppodI43F1JJvX8sQVmZlUG3sCStZIPAQ4HyJoWh1eRKM89nLSGzupQsmo7n+o/gxfAVGjnKCCcX3JYq0QmxHMmYZe0eaMgsX0YHPmrSICOdPOK9qduwhBLTjRF6kf8BiGmGSEZhESg8OeVUIEQgxygPmL9Y69MO/1xDNnmnZF+28bLz+QjuMxDDHJCMvcB1KjDnjoGLtNBEIMMsAxGmobD5j0rAoikDMNuKLttC17SAfWHsaGBCiGISYZMTSxVtLAZrorhjFEMQxtAZxgx1saCEwGNa8YHwWd668Va5XkSVQOdyvSeqIoYhZvU50uL6EEnIDgaCwC6cfjfPnOf2qgaH69HaeP//6Kx/9b65tUASXmceKTxgljU40P4R8cCwKX41Gex4kbjBMuJEFMqUuGAqnSLLcxJE/E/zpOiDDI5e4EvGQmsCIV7W1lHifmcaL7Oo8T8zgxjxPzOLH4rHHi+S8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t, Column10 = _t, Column11 = _t, Column12 = _t, Column13 = _t, Column14 = _t, Column15 = _t, Column16 = _t, Column17 = _t, Column18 = _t, Column19 = _t])
Hi Anonymous
The simplest method is to create a new query to connect to the local Excel file first, then copy its first several steps code into the GoogleSheet query to replace the first Source step. Don't forget to add a comma at the end of the last step you pasted.
Sometimes the auto-generated "Changed Type" step doesn't change data types correctly, you may need to adjust its code, or remove it then change data types manually to make sure all columns have proper data types before the subsequent transformations.
Duplicate the Googlesheet query to perform the replacement in case that you lose everything.
Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!
1 Reply
Hi Anonymous
The simplest method is to create a new query to connect to the local Excel file first, then copy its first several steps code into the GoogleSheet query to replace the first Source step. Don't forget to add a comma at the end of the last step you pasted.
Sometimes the auto-generated "Changed Type" step doesn't change data types correctly, you may need to adjust its code, or remove it then change data types manually to make sure all columns have proper data types before the subsequent transformations.
Duplicate the Googlesheet query to perform the replacement in case that you lose everything.
Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!