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 have one Excel for each store that I have (store 1.xls; store 2.xls; store 3.xls)
I have imported every file into BI in a diferent table because some stores have more information that another, for example: observation field
To consolidate all store into one table
1) I have created an Index like: [date] & "#" & [productID]
2) Then create a new Table that consolidate with DAX : FILTER(DISTINCT(UNION(DISTINCT(store1[INDEX_DATE_PRODUCT]);DISTINCT(store2[INDEX_DATE_PRODUCT])));NOT(ISBLANK([INDEX_DATE_PRODUCT])))
3) Then create a new column with DAX: CALCULATE(SUM(...);FILTER(...))
4) Then split the Index with DAX: PATHITEM(SUBSTITUTE(summary[INDEX_DATE_PRODUCT];"#";"|");1)
Question¹: There is a way to do this with less steps or smarter?
Main question: I have an unique ProductID from each store. How can I have a column with an origin destionation table.
Example merged table:
| INDEX_DATE_PRODUCT | SUM_QUANTITY | DATE | PRODUCT_ID | ORIGIN
| .... | 100.000 | 2015-01-05 | axb001241 | store1
| .... | 150.000 | 2015-01-05 | axb0a1241 | store2
| .... | 280.000 | 2015-01-05 | aab001000 | store1
In this scenario, firstly I suggest you adding calculated column or removing extra column to keep same structure of all three dataset. Then you can just go to Query Editor and use "Append Queries" to combing all three tables together. Please see: Shape and combine data in Power BI Desktop
After that, you do modeling like adding Index column on this combined dataset.
Regards,
Thanks for your reply @v-sihou-msft. But how can I get a column with info about the origin table, like a destination table?
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 |
---|---|
107 | |
93 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |