Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi to all,
I have a sharePoint List, which represents our running costs like insurance, personnel coast or leasing coasts.
It looks like this:
...
Title | costs | first payment | month cycle | number of payments |
employee 1 | 2000 | 01.01.2017 | 1 | 9 |
insurance 1 | 3500 | 01.02.2017 | 6 | 2 |
now I need to transform the table to something like this:
Date Costs Title
Date | costs | Titel |
01.01.2017 | 2000 | Employee1 |
01.02.2017 | 2000 | Employee1 |
01.02.2017 | 3500 | insurance |
01.03.2017 | 2000 | Employee1 |
01.04.2017 | 2000 | Employee1 |
01.05.2017 | 2000 | Employee1 |
01.06.2017 | 2000 | Employee1 |
01.07.2017 | 2000 | Employee1 |
01.08.2017 | 2000 | Employee1 |
01.08.2017 | 3500 | insurance |
01.09.2017 | 2000 | Employee1 |
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!
Solved! Go to Solution.
Hi @fehr_da,
Please try this workaround.
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
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
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/2016 | 9 |
02/05/2017 | 7 |
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.
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
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |