Creating Master Data tables from Transaction data file

[Prasad1]

Hi

I have a huge Sales transaction file which contains many columns from Company code to SKU code with Codes and Descriptions across multiple fields (From A to Y as showed in the attachment). I want to create mulgipld 'Master Data' Dimension tables and 'Transaction Data' fact table from this Sales file. For e.g. Sales file contains Warehouse IDs, Warehouse Descriptions (repeated multiple times for different Products under same warehouse). I want to create 'Warehouse Master' Dimension table by taking Distinct values of Warehouse ID, Warehouse Descriptions from the sales file.

Similarly from the sales file,  I want to create

a) 'Category Master' Dimension table by taking distinct values of Category ID, Category Description

b) 'Source Country Code Master' Dimension table by taking distinct values of Source Country ID, Source Country Description

c) 'Product Master' Dimension table by taking distinct values of Product ID, Product Description

 and so on.

Doing it with excel is tedious and time consuming by copying the transaction file multiple times, keeping relevant columns by removing other columns and deleting duplicates etc. Is there a better, easy way to do it in Power BI?

Thanks for your help in advance

Prasad

[erik_tarnvik]

Hi @Prasad1,

I just wrote an example for someone else asking what I think is the same or at least a very similar question. Hopefully you will find it helpful.

https://community.powerbi.com/t5/Desktop/power-bi-scripting-data-load/td-p/272021

[Prasad1]

Hi

I tried using Distinct function for creating the master table. But Distinct gives only one column with distinct values. I want the Code as well as the Description in one Master Dimension table.

Thanks

Prasad

[Anonymous]

Hi @Prasad1

Can you provide a sample record set of say 1000 rows in excel format. Load the same in One Drive or Google Drive and share the link.

It is possible to do it using PowerQuery.

Cheers

CheenuSing