Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
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"}),
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
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.
@HarshKalra , refer if this can help
https://blog.crossjoin.co.uk/2019/05/19/storing-large-images-in-power-bi-datasets/
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |