Please help me. I'm new to Power Bi and I've tried to transform data but it's not behaving the way I would like for all the scenarios in my data.... I have both fiscal and calendar years listed in various formats as follows and I'm purely trying to extract the year or year range. Can anyone help?
ProjectCode | Desired Outcome = 'Tax Year' |
2016 FBAR | 2016 |
2018 K-1 | 2018 |
2018-2021 5471 | 2018-2021 |
2022.23 UK Return | 2022.23 |
2017 1040 | 2017 |
2016-2021 FBAR | 2016-2021 |
Internal Project | null |
1040 Proforma | null |
Corporate Consulting | null |
2012.13-2015.16 UK Return | 2012.13-2015.16 |
2017 Consulting | 2017 |
2015 & 2016 1040 | 2015-2016 |
I transformed the date using the 'Add Column from Example' and this is the string used:
= Table.AddColumn(dbo_INTERIMWORKINGPROJECT, "Tax Year", each let splitProjectCode = Splitter.SplitTextByDelimiter(" ", QuoteStyle.None)([ProjectCode]), splitsplitProjectCode0 = Splitter.SplitTextByDelimiter(".", QuoteStyle.None)(splitProjectCode{0}?), splitProjectCode2 = List.Reverse(Splitter.SplitTextByDelimiter(" ", QuoteStyle.None)([ProjectCode])) in Text.Combine({Text.Combine(List.Transform(splitsplitProjectCode0, each Text.Start(_, 4)), "."), Text.Middle(splitProjectCode2{1}?, 4)}), type text)
The majority worked but examples of the issues are listed below. Can anyone help me fix the string above as I'm going round in circles?
ProjectCode | Tax Year |
2013-2015 3520A SL | 2013A |
2017 Consulting SH | 2017ulting |
2012 NY State Return | 2012e |
2013-2015 1040SL Rev | 2013SL |
2011.12-2016.17 UK | 2011.12-2.17 |
2016.17 Amended UK | 2016.17ded |
2017 3520/3520A Rev | 2017/3520A |
2015 & 2016 1040 | 2015 |
2016-2018 1040X SL | 2016X |
2016 FACTA Report | 2016A |
Thank you!
