How to do Large Image Transformations for Country Dimension Table

GarryA · October 20, 2019, 12:50pm

I’m looking to complete a fairly simple transformation in a country table from this dataset.

I wanted to confirm what Power BI users thought a good recommended approach for this scenario would be?

I want to show a SINGLE country on the ROW with an index (from 1) and the PIC on the column and repeated as a column if required. ie Pic1, Pic2, Pic3, Pic4 etc. Here is the current position of the data.

I hoping to make this a dynamic country dimension table with Base64 picture encoding per Chris Webb’s Binary to Image post.

Per Chris’s blog some image names (country names) can be repeated if the image size is larger then the limitations. You can see Europe for example is repeated 4 times so it would need 4 columns to hold each of the repeated rows with the PIC data transposed to each new column. In contrast a smaller picture size only need a single row.

What is the best M code to use to complete this transformation in the Query Editor?

Paul · October 21, 2019, 1:49pm

Hi,
See attached, is this what you are looking for?

Paul
country dimension example.xlsx (26.3 KB)

GarryA · October 21, 2019, 2:39pm

@Paul Thanks I need a single row with Country and duplicated column with each pic field

Paul · October 22, 2019, 2:35pm

Hi

Will this help?

Paul
country dimension example 2.xlsx (24.3 KB)

GarryA · October 22, 2019, 3:20pm

@Paul Yes you have the row repeated once and PIC repeated in column which is correct result.

Paul · October 23, 2019, 1:39pm

Hi,
Just to show a proper table, I have made some changes.
Please confirm this is what you are after.
Paul
country dimension example 3.xlsx (25.7 KB)

GarryA · October 24, 2019, 1:41pm

Sure looks good what transformations did you use?

Paul · October 24, 2019, 1:50pm

I take it you have opened the query editor and followed the steps?

GarryA · October 24, 2019, 2:01pm

Hi @Paul

Thanks for taking time to answer this post but I have found its always better/easy to provide the steps you have taken instead of an attachment which makes people have to download.

Besides your spreadsheet is not English so I cant read the power query inside.

Images or write code in the post always make it easier for users to see comments and how problems were solved too.