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
fehr_da
Frequent Visitor

Restructure table of running costs

Hi to all,

 

I have a sharePoint List, which represents our running costs like insurance, personnel coast or leasing coasts.

It looks like this:

 

 

...

 

Titlecostsfirst paymentmonth cyclenumber of payments
employee 1200001.01.201719
insurance 1350001.02.201762

 

 

now I need to transform the table to something like this:

 

 

Date Costs Title

DatecostsTitel
01.01.20172000Employee1
01.02.20172000Employee1
01.02.20173500insurance
01.03.20172000Employee1
01.04.20172000Employee1
01.05.20172000Employee1
01.06.20172000Employee1
01.07.20172000Employee1
01.08.20172000Employee1
01.08.20173500insurance
01.09.20172000Employee1

 

is there a way to do this and if, is there a way to do it directly in the Query-Editor?

 

Thank you very much for your help!

 

1 ACCEPTED SOLUTION

Hi @fehr_da,

 

Please try this workaround.

1.PNG

 

let
    Source = Excel.Workbook(File.Contents("C:\Users\v-yulgu\Desktop\Sample Data.xlsx"), null, true),
    Restructure_Sheet = Source{[Item="Restructure",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Restructure_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Title", type text}, {"costs", Int64.Type}, {"first payment", type date}, {"month cycle", Int64.Type}, {"number of payments", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "All Month", each {1..12}),
    #"Expanded All Month" = Table.ExpandListColumn(#"Added Custom", "All Month"),
    #"Added Custom1" = Table.AddColumn(#"Expanded All Month", "first payment+number of payments", each Date.AddMonths([first payment],(([number of payments]-1)*[month cycle]))),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "first payment+All Month", each Date.AddMonths([first payment],(([All Month]-1)*[month cycle]))),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom2", each [#"first payment+number of payments"] >= [#"first payment+All Month"])
    
in
   #"Filtered Rows"

Regards,
Yuliana Gu

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

View solution in original post

4 REPLIES 4
v-yulgu-msft
Employee
Employee

Hi @fehr_da,

 

Just a little modification based on the solution provided by Excelside:

 

Custom column=List.Dates( [first payment] , [number of payments] , #duration([month cycle],0,0,0) )

Regards,
Yuliana Gu

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

Yes, you can use Power Query.

 

1) In the query editor, add a column which stores the list or required dates

Column = List.Dates( [Date] , [NumberPayments] , #duration(1,0,0,0) )

 

2) Then expand this list.

 

Here is an example of Code I generated with this database as a starting point (Source)

Dates              Nb

01/01/20169
02/05/20177

 

let

Source = "The Table Above"
ModifType = Table.TransformColumnTypes(Source,{{"Dates", type date}, {"Nb", Int64.Type}}),
DatesColumn = Table.AddColumn(ModifType, "ListOfDates", each List.Dates([Dates],[Nb],#duration(1,0,0,0))),
Expansion = Table.ExpandListColumn(DatesColumn, "ListOfDates")
in
Expansion

Thanks a lot, works good, but there is an issue:

 

#duration only increases per days, not per months. As every month has a different amount of days, I need to go a little bit deeper.

 

Now I tried to generate a List of Months like this

PaymentMonths = List.Generate(()=>[MonthStart], each _ < [MonthEnd], each _ +1)

Where MonthStart is e.g. 8 (=August) and MonthEnd is 12 (=Dezember).

 

Expectet ouput is a List containing: 8,9,10,11,12.

 

Unfortunally I get an Error Message in German :-), so it won't help much I guess.

 

But I lokated the Error in the second argument, as something like

 

PaymentMonths = List.Generate(()=>[MonthStart], each _ < 20, each _ +1)

works fine without errors

 

 

any sugestions?

Hi @fehr_da,

 

Please try this workaround.

1.PNG

 

let
    Source = Excel.Workbook(File.Contents("C:\Users\v-yulgu\Desktop\Sample Data.xlsx"), null, true),
    Restructure_Sheet = Source{[Item="Restructure",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Restructure_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Title", type text}, {"costs", Int64.Type}, {"first payment", type date}, {"month cycle", Int64.Type}, {"number of payments", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "All Month", each {1..12}),
    #"Expanded All Month" = Table.ExpandListColumn(#"Added Custom", "All Month"),
    #"Added Custom1" = Table.AddColumn(#"Expanded All Month", "first payment+number of payments", each Date.AddMonths([first payment],(([number of payments]-1)*[month cycle]))),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "first payment+All Month", each Date.AddMonths([first payment],(([All Month]-1)*[month cycle]))),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom2", each [#"first payment+number of payments"] >= [#"first payment+All Month"])
    
in
   #"Filtered Rows"

Regards,
Yuliana Gu

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

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.