Hi,
All my files are on my Onedrive, and I'm using 2 PCs (home & work).
I used the method shown in lesson 2.08 to get the file's path from Onedrive, although Mynda specify it Onedrive for Buesiness, and it didn't work.
In my Onedrive I don't have the "Copy Path" that shows in lesson 2.08.
I tried using the share option and all kinds of other options, but none was right.
I have Onedrive as part of my "365 Family" account.
And again, any help would be appreciated.
Regards,
Ayal Telem.
Hi,
I found a solution:
Get data from OneDrive Personal files with Power Query (exceloffthegrid.com)
Regards,
Ayal Telem.
BTW, I created a PQ to handle converting the Embed path to the path that can be used (use the attached file as a sample):
let
Source = Excel.CurrentWorkbook(),
#"Removed Columns" = Table.RemoveColumns(Source,{"Name"}),
#"Expanded Content" = Table.ExpandTableColumn(#"Removed Columns", "Content", {"File", "Path", "Embed Path"}, {"File", "Path", "Embed Path"}),
#"Duplicated Column" = Table.DuplicateColumn(#"Expanded Content", "Embed Path", "Embed Path - Copy"),
#"Renamed Columns" = Table.RenameColumns(#"Duplicated Column",{{"Embed Path - Copy", "Correct Path"}}),
#"Replaced Value" = Table.ReplaceValue(#"Renamed Columns","<iframe src=""","",Replacer.ReplaceText,{"Correct Path"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","embed","download",Replacer.ReplaceText,{"Correct Path"}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value1", "Correct Path", Splitter.SplitTextByEachDelimiter({""" width="}, QuoteStyle.None, true), {"Correct Path.1", "Correct Path.2"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Correct Path.1", type text}, {"Correct Path.2", type text}}),
#"Removed Columns1" = Table.RemoveColumns(#"Changed Type",{"Correct Path.2"}),
#"Renamed Columns1" = Table.RenameColumns(#"Removed Columns1",{{"Correct Path.1", "Correct Path"}})
in
#"Renamed Columns1"
Not sure this is the smartest solution, but for a PQ novice it will suffice.
Regards,
Ayal Telem.
Nice! Thanks for sharing, Ayal
The least I could do.
