Forum Discussion
Post ProdigyAdd month in power query based on year
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.
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/
8 Replies
- VijayP
Community Champion
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
- DataZoe
Microsoft Employee
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"- o59393
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!
