Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
o59393
Post Prodigy
Post Prodigy

Add 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?

 

year.JPG

 

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:

 

yearMonthfacility_idVolumen Sold (ML)Volume Replenished (ML)
2020January640130.17212593110.5
2020February640130.17212593110.5
2020March640130.17212593110.5
2020April640130.17212593110.5
2020May640130.17212593110.5
2020June640130.17212593110.5
2020July640130.17212593110.5
2020August640130.17212593110.5
2020September640130.17212593110.5
2020October640130.17212593110.5
2020November640130.17212593110.5
2020December640130.17212593110.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.

1 ACCEPTED 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/

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.

View solution in original post

8 REPLIES 8
DataZoe
Employee
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"

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:

 

rep.JPG

 

Thanks!

VijayP
Super User
Super User

@o59393 

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




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

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

 




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!


Hi to have the table look like this:

 

yearMonthfacility_idVolumen Sold (ML)Volume Replenished (ML)
2020January640130.17212593110.5
2020February640130.17212593110.5
2020March640130.17212593110.5
2020April640130.17212593110.5
2020May640130.17212593110.5
2020June640130.17212593110.5
2020July640130.17212593110.5
2020August640130.17212593110.5
2020September640130.17212593110.5
2020October640130.17212593110.5
2020November640130.17212593110.5
2020December640130.17212593110.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/

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.

Hi @v-eachen-msft 

 

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 😄

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.