Display large size images off the SharePoint

[HarshKalra]

I’m trying to solve a problem in Power BI where I need to display large size images off the SharePoint. I am able to connect and display the images of up to 30kb, but anything larger size need to have an M query developed to breakdown the URL binary as text into smaller sizes and then can be concatenate via DAX. I really don't know how to write the M query and make it work (I got it from https://blog.crossjoin.co.uk/2019/05/19/storing-large-images-in-power-bi-datasets/)

Would you please help me to check what’s wrong with  M query code , can’t get it to work. Really appreciate if you can help me or point me to the right direction.

[v-eachen-msft]

Hi @HarshKalra ,

Please refer to the following codes:

Source = SharePoint.Files("Your sharepoint root directory", [ApiVersion = 15]),
#"Filtered Rows1" = Table.SelectRows(Source, each ([Folder Path] = "Your entire sharepoint image directory")),
RemoveOtherColumns = Table.SelectColumns(#"Filtered Rows1",{"Content", "Name"}),

Replace the following codes in the blog with the above codes.

Source = Folder.Files("C:\Users\Chris\Documents\PQ Pics"),
RemoveOtherColumns = Table.SelectColumns(Source,{"Content", "Name"}),

[HarshKalra]

Sorry I am lost becasue I'm still learning

Literally I followed Chris's blog to attempt solve the probelm, if there is simple way I'm keen to follow. Here is what I did, created a blank query

= let
//Get files from the Folder
Source = SharePoint.Files("https://xxxxxxxx.sharepoint.com/sites/xxxxxx",[ApiVersion = 15]),
#"xxxx" = TableTable.SelectRows(Source, each([/Shared%20Documents/General/Pictures/] = "https://xxxxxxxx.sharepoint.com/sites/xxxx/Shared%20Documents/General/Pictures")),
//creates Splitter function
SplitTextFunction = SplitterSplitter.SplitTextByRepeatedLengths(30000),
//Converts table of files to list
ListInput = TableTable.ToRows
//Function to conver binary of photo to multiple
//text values
ConvertOneFile = (InputRow as List) =>
let
BinaryIn = InputRow{0},
FileName = InputRow{1},
BinaryText = Binary.ToText(BinaryEncoding.Base64),
SplitUpText = SplitTextFunction(BinaryText),
AddFileName = List.Transform(SplitUpText, each {Filename,_})
in
AddFileName,
//Loops over all photos and call the above function
ConvertAllFiles = List.Transform(ListInput, each ConvertOneFile(_)),
//Combine lists together
CombineList = List.Combine(ConvertAllFiles),
//Conoverts results to table
ToTable = #"xxxx"(type table[Name=text,Pic=text],CombineLists),
//Adds index column to output table
AddIndexColumn = Table.AddIndexColumn(ToTable,"Index",0,1)
in
AddIndexColumn

[v-eachen-msft · Accepted solution]

Hi @HarshKalra ,

Please use the following codes:

ListInput = Table.ToRows(#"xxxx"),

Your "ListInput = TableTable.ToRows" is incorrect. It needs your previous step name as a parameter.

[amitchandak]

@HarshKalra , refer if this can help

https://blog.crossjoin.co.uk/2019/05/19/storing-large-images-in-power-bi-datasets/

[HarshKalra]

I actully refered the link and got stuck to make it work, something isn't adding up on M query. Would you be able to show me steps how to write the M query, is it a blank query you add in the Transform data, the codes were not working. Apprecite your guidance, I'm quite new to write M query, can work with DAX measures.