Power Query Source Links Errors how to fix?

[daleholden]

HI Guys

I have several Excel workbooks stored in OneDrive which I access in my home via Desktop or Laptop. I have noticed when I open my files on my laptop all my power query error as the link to the folder is based on my Desktop.I would have thought using Onedrive to store all my files it would work on any machine. What am I doing wrong I am not trying to share with other just myself.
I have seen several very convoluted ways to share with other team members. But mine is not that just myself using the same OneDrive account.

Dale

 

[Comfy]

How are you connecting to the file??

Have you installed the OneDrive app on you PC and using that or are you connecting to one drive using a URL?

I suspect you are using the APP.

This creates a folder on your PC and uploads any changes to Onedrive when a connection is available.

This blog post looks to contain everything you need: Using Power Query to Get Data From an Excel File in OneDrive Via URL — SQL Chick

 

[daleholden]

I have already read that yesterday and tried it but it no longer works. My links are to folders with multiple CSV files in. These folders do not work with resid or id. I cannot believe something as basic as people on the move wanting to use does not work with OneDrive or you have to go round the houses. I would go round the houses but it will not work for me I do not get the same URL for folders with files in.

 

[Comfy]

I will look at the URL solution when I'm at home.

I cannot believe something as basic as people on the move wanting to use does not work with OneDrive

It's not that basic...

Can you paste your M code here please?

Is the OneDrive app installed on both PC and Laptop.

We can do one of two things:

* Ensure that the setup is exactly the same on both machines
* Update your M so that it connects to different folders depending on which Machine you are using.

My links are to folders with multiple CSV files in. These folders do not work with resid or id

This is to be expected. The OneDrive App and OneDrive are not the same thing.

OneDrive is a cloud based hosting solution that you connect to using a URL
The OneDrive app runs on your local machine and monitors a folder/folders. It then regularly checks for updates and uploads the files in those folders to the OneDrive cloud.

 

[daleholden]

Hi Comfy
I have OneDrive on both laptop and my 2 Desktops they have different paths not C:/ on all one is G:/ one at C:/ and D:/ also i have iPad and iPhone.
I have a Workbook with about 7 queries that point to OneDrive app. I have tried for 2 days with different urls per the blog but nothing works.
I have not been able to get a url for the folders??



let
Source = Folder.Files("G:\OneDrive\Documents\Excel Files\Anditsgone Excell and CSV Files\CSV Files\eBay Invoices"),
#"Combined Binaries" = Binary.Combine(Source[Content]),
#"Imported CSV" = Csv.Document(#"Combined Binaries",[Delimiter=",", Columns=8, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Changed Type" = Table.TransformColumnTypes(#"Imported CSV",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}}),
#"Removed Top Rows" = Table.Skip(#"Changed Type",7),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows"),
#"Filtered Rows" = Table.SelectRows(#"Promoted Headers", each ([Fee type] <> "" and [Fee type] <> "Fee type" and [Fee type] <> "N/A")),
#"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows","Date",Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false),{"Date.1", "Date.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Date.1", type date}, {"Date.2", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Date.2", "VIN/Serial number"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns",{{"Date.1", type date}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{"Date.1", "Date"}, {"Item", "eBay Listing Number"}}),
#"Changed Type3" = Table.TransformColumnTypes(#"Renamed Columns",{{"Amount", Currency.Type}, {"Promotional savings", Currency.Type}}),
#"Filtered Rows1" = Table.SelectRows(#"Changed Type3", each ([Amount] <> null)),
#"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows1",{"Promotional savings", "ReceivedTopRatedDiscount"})
in
#"Removed Columns1"

 

[Comfy]

This isn't the correct way to deal with this but can't access OneDrive where I am to test an alternative.

let
    LaptopPath = "C:\OneDrive\Documents\Excel Files\Anditsgone Excell and CSV Files\CSV Files\eBay Invoices",
    Desktop1Path = "D:\OneDrive\Documents\Excel Files\Anditsgone Excell and CSV Files\CSV Files\eBay Invoices",
    Desktop2Path = "G:\OneDrive\Documents\Excel Files\Anditsgone Excell and CSV Files\CSV Files\eBay Invoices",


    LaptopTest = try List.Count(Folder.Files(LaptopPath)[Content]),
    Desktop1Test = try List.Count(Folder.Files(Desktop1Path)[Content]),
    Desktop2Test = try List.Count(Folder.Files(Desktop2Path)[Content]),


    Source = if LaptopTest[HasError] then 
                if Desktop1Test[HasError] then 
                    if Desktop2Test[HasError] then
                        "Cannot find Data Source"
                    else
                    Folder.Files(Desktop2Path)
                else
                Folder.Files(Desktop1Path)
            else
            Folder.Files(LaptopPath)


#"Combined Binaries" = Binary.Combine(Source[Content]),
#"Imported CSV" = Csv.Document(#"Combined Binaries",[Delimiter=",", Columns=8, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Changed Type" = Table.TransformColumnTypes(#"Imported CSV",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}}),
#"Removed Top Rows" = Table.Skip(#"Changed Type",7),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows"),
#"Filtered Rows" = Table.SelectRows(#"Promoted Headers", each ([Fee type] <> "" and [Fee type] <> "Fee type" and [Fee type] <> "N/A")),
#"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows","Date",Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false),{"Date.1", "Date.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Date.1", type date}, {"Date.2", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Date.2", "VIN/Serial number"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns",{{"Date.1", type date}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{"Date.1", "Date"}, {"Item", "eBay Listing Number"}}),
#"Changed Type3" = Table.TransformColumnTypes(#"Renamed Columns",{{"Amount", Currency.Type}, {"Promotional savings", Currency.Type}}),
#"Filtered Rows1" = Table.SelectRows(#"Changed Type3", each ([Amount] <> null)),
#"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows1",{"Promotional savings", "ReceivedTopRatedDiscount"})
in
#"Removed Columns1"

Adjust the filepath of the first three lines accordingly.

 

[daleholden]

Hi Comfy

Would you prefer for me to wait well you have had a chance to test with OneDrive?

To try you code do I need to alter the names of the Pc's to match what OneDrive has them named as in Pc's list?

 

[Comfy]

You're using one drive to sync a file over multiple devices so the file is stored on you PC/Laptop.

All you need to do is change the file paths of the first three lines to where they are on your Desktops and Laptop.

 

[daleholden]

Does this look correct

DALE-LAPTOP = "D:\OneDrive\Documents\Excel Files\Anditsgone Excell and CSV Files\CSV Files\eBay Invoices",
Dale-PC1-1 = "G:\OneDrive\Documents\Excel Files\Anditsgone Excell and CSV Files\CSV Files\eBay Invoices",



DALE-LAPTOP = try List.Count(Folder.Files(LaptopPath)[Content]),
Dale-PC1-1 = try List.Count(Folder.Files(Desktop1Path)[Content]),




Source = if DALE-LAPTOP[HasError] then
if Dale-PC1-1[HasError] then
"Cannot find Data Source"
else
Folder.Files("G:\OneDrive\Documents\Excel Files\Anditsgone Excell and CSV Files\CSV Files\eBay Invoices")
else
Folder.Files("D:\OneDrive\Documents\Excel Files\Anditsgone Excell and CSV Files\CSV Files\eBay Invoices")

 

[Comfy]

Does this look correct

Nope

Did you need to change all the names?

You only need to change these (Bold)


LaptopPath = "C:\OneDrive\Documents\Excel Files\Anditsgone Excell and CSV Files\CSV Files\eBay Invoices",
Desktop1Path = "D:\OneDrive\Documents\Excel Files\Anditsgone Excell and CSV Files\CSV Files\eBay Invoices",
Desktop2Path = "G:\OneDrive\Documents\Excel Files\Anditsgone Excell and CSV Files\CSV Files\eBay Invoices",