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.
Hi everyone,
I am looking for a solution to add new rows to my dataset.
For each Month of each value of [Country Name], I need to ensure that there is a row where [Business] = "Mining". If it does not in dataset, I need to add a new one. As in the picture below, there is row for every month of Country1, but only row for January and December of Country2. So I need to add more 10 rows (from February to November). However, it possibly required 3 or different number of rows for other [Country Name] value, or might be none (if like Country1).
I tried to divide this problem into small steps, both in Power Query or DAX, but till now I cannot find any reasonable solution.
Any help from you would be so valuable to me!
Thank you!
Solved! Go to Solution.
Hi @Anonymous
In Edit queries, i create a date table as below
enter dates in box "startdate" and "enddate", then "Invoke", we can get a new table, renamed it as "date",
Then in your table, if you only have three columns, group data as below,
Then add a custom column and expand
Hi @Anonymous
In Edit queries, i create a date table as below
enter dates in box "startdate" and "enddate", then "Invoke", we can get a new table, renamed it as "date",
Then in your table, if you only have three columns, group data as below,
Then add a custom column and expand
@v-juanli-msft thank you Juan li, there are much power query techniques on it and I have learned a lot. Very sorry for the late reaction.
Hi @Anonymous ,
I think the easiest solution is the generate a new table in DAX with all the necessary rows.
You can create a new calculated table in Power BI with the following formula:
Country by month table = GENERATEALL(VALUES(Table1[Country Name]),VALUES(Table1[Calendar]))
You can then add columns to this new table and copy there any values from your previous table.
Does this help you?
LC
Interested in Power BI and DAX templates? Check out my blog at www.finance-bi.com
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 |
---|---|
110 | |
97 | |
78 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |