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 all
How can I add with Power Query a step to add the months for each row that contains the year?
So for example in the first row that contains the code 6401, it should have all the 12 months for that faciliy ids and so on:
year | Month | facility_id | Volumen Sold (ML) | Volume Replenished (ML) |
2020 | January | 6401 | 30.17212593 | 110.5 |
2020 | February | 6401 | 30.17212593 | 110.5 |
2020 | March | 6401 | 30.17212593 | 110.5 |
2020 | April | 6401 | 30.17212593 | 110.5 |
2020 | May | 6401 | 30.17212593 | 110.5 |
2020 | June | 6401 | 30.17212593 | 110.5 |
2020 | July | 6401 | 30.17212593 | 110.5 |
2020 | August | 6401 | 30.17212593 | 110.5 |
2020 | September | 6401 | 30.17212593 | 110.5 |
2020 | October | 6401 | 30.17212593 | 110.5 |
2020 | November | 6401 | 30.17212593 | 110.5 |
2020 | December | 6401 | 30.17212593 | 110.5 |
If possible I would like to see the extra step as a new column called month that contains the respective month?
Pbix: https://1drv.ms/u/s!ApgeWwGTKtFdhl9OEC9uP6RgIO2z?e=3piN5m
Thanks.
Solved! Go to Solution.
Hi @o59393 ,
I think you want to cross join the two tables, here is a helpful blog for your reference.
https://exceleratorbi.com.au/cross-join-with-power-query/
Ok Mscript is not my strength so someone could probably improve it, but this should get you there:
let
Source = Csv.Document(File.Contents("C:\data.csv"),[Delimiter=",", Columns=4, Encoding=65001, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"year", Int64.Type}, {"facility_id", Int64.Type}, {"Volumen Sold (ML)", type number}, {"Volume Replenished (ML)", type number}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Month", each "Jan"),
#"Appended Query" = Table.Combine({#"Added Custom", Table.AddColumn(#"Changed Type", "Month", each "Feb")}),
#"Appended Query1" = Table.Combine({#"Appended Query", Table.AddColumn(#"Changed Type", "Month", each "Mar")}),
#"Appended Query2" = Table.Combine({#"Appended Query1", Table.AddColumn(#"Changed Type", "Month", each "Apr")}),
#"Appended Query3" = Table.Combine({#"Appended Query2", Table.AddColumn(#"Changed Type", "Month", each "May")}),
#"Appended Query4" = Table.Combine({#"Appended Query3", Table.AddColumn(#"Changed Type", "Month", each "Jun")}),
#"Appended Query5" = Table.Combine({#"Appended Query4", Table.AddColumn(#"Changed Type", "Month", each "Jul")}),
#"Appended Query6" = Table.Combine({#"Appended Query5", Table.AddColumn(#"Changed Type", "Month", each "Aug")}),
#"Appended Query7" = Table.Combine({#"Appended Query6", Table.AddColumn(#"Changed Type", "Month", each "Sep")}),
#"Appended Query8" = Table.Combine({#"Appended Query7", Table.AddColumn(#"Changed Type", "Month", each "Oct")}),
#"Appended Query9" = Table.Combine({#"Appended Query8", Table.AddColumn(#"Changed Type", "Month", each "Nov")}),
#"Appended Query10" = Table.Combine({#"Appended Query9", Table.AddColumn(#"Changed Type", "Month", each "Dec")})
in
#"Appended Query10"
Respectfully,
Zoe Douglas (DataZoe)
Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/
hi all
I finally got it to work with @v-eachen-msft
I created a second table containing the 12 months, then I added a new column and inserted the table created with the months to get this:
Thanks!
In First Place I cannot use the PBIX as it is connected server(cloud data).
Secondly if some how i convert that facility ID for 12 months ( i.e., 12 rows) what should be the amount . since you donot have a date and this is already aggregated to one year data, it is not possible.
Can you explain why you want that information in 12 rows each ID and how to deal with the value
Proud to be a Super User!
hi @VijayP
I reprashed my statment please have a look.
About the pbix, it's on one drive. Do you have issues downloading it?
Thanks.
@o59393 I have Downloaded but I cannot view that.
for the DAte column , do you want alongwith 2020 JAn 1 2020, is that the case or any other way. we can use mquery or custom column but need to what exactly in that row required
Proud to be a Super User!
Hi to have the table look like this:
year | Month | facility_id | Volumen Sold (ML) | Volume Replenished (ML) |
2020 | January | 6401 | 30.17212593 | 110.5 |
2020 | February | 6401 | 30.17212593 | 110.5 |
2020 | March | 6401 | 30.17212593 | 110.5 |
2020 | April | 6401 | 30.17212593 | 110.5 |
2020 | May | 6401 | 30.17212593 | 110.5 |
2020 | June | 6401 | 30.17212593 | 110.5 |
2020 | July | 6401 | 30.17212593 | 110.5 |
2020 | August | 6401 | 30.17212593 | 110.5 |
2020 | September | 6401 | 30.17212593 | 110.5 |
2020 | October | 6401 | 30.17212593 | 110.5 |
2020 | November | 6401 | 30.17212593 | 110.5 |
2020 | December | 6401 | 30.17212593 | 110.5 |
Can it be done without M query?
Thanks.
Hi @o59393 ,
I think you want to cross join the two tables, here is a helpful blog for your reference.
https://exceleratorbi.com.au/cross-join-with-power-query/
I would like to have the year expanded with its 12 months, with Power Query steps.
So for each of column called facility id, it should have 12 rows (from Jan to Dec)
I saw a function called Date.AddMonths https://docs.microsoft.com/en-us/powerquery-m/date-addmonths
Could it be used here?
Thanks 😄
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 |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |