Power Query conversion from single columns to multiple columns based on rowname index

Question

For my datamodel I need to convert a table I fetch from a database into a more easy format to work with in Power BI.

The table I receive from the database is this one. (after some cleanup and transposing it)

CurYrPrdStat1	C
CurYrPrdStat2	C
CurYrPrdStat3	O
CurYrPrdStat4	C
CurYrPrdStat5	C
CurYrPrdStat6	C
CurYrPrdStat7	C
CurYrPrdStat8	C
CurYrPrdStat9	C
CurYrPrdStat10	C
CurYrPrdStat11	C
CurYrPrdStat12	C
CurYrPrdStat13	C
CurYrPrdStat14
CurYrPrdStat15	C
CurYrPrdEnd1	31/07/2018 00:00
CurYrPrdEnd2	31/08/2018 00:00
CurYrPrdEnd3	30/09/2018 00:00
CurYrPrdEnd4	31/10/2018 00:00
CurYrPrdEnd5	30/11/2018 00:00
CurYrPrdEnd6	31/12/2018 00:00
CurYrPrdEnd7	31/01/2019 00:00
CurYrPrdEnd8	28/02/2019 00:00
CurYrPrdEnd9	31/03/2019 00:00
CurYrPrdEnd10	30/04/2019 00:00
CurYrPrdEnd11	31/05/2019 00:00
CurYrPrdEnd12	30/06/2019 00:00
CurYrPrdEnd13	30/06/2019 00:00
CurYrPrdEnd14	null
CurYrPrdEnd15	null

Using power Query, I would like to achieve following format:

Period	CurrentPeriodEndDate	CurrentStatus
Period 1	31/07/2018 00:00	C
Period 2	31/08/2018 00:00	C
Period 3	30/09/2018 00:00	O
Period 4	31/10/2018 00:00	C
Period 5	30/11/2018 00:00	C
Period 6	31/12/2018 00:00	C
Period 7	31/01/2019 00:00	C
Period 8	28/02/2019 00:00	C
Period 9	31/03/2019 00:00	C
Period 10	30/04/2019 00:00	C
Period 11	31/05/2019 00:00	C
Period 12	30/06/2019 00:00	C
Period 13	30/06/2019 00:00	C
Period 14	null
Period 15	null	C

So basically what I would like to achieve is using the index number (eg. CurYrPrdEnd1) and use that one as a value.

I tried but do not really see how I could convert this using the power query editor.

After that I would like to use this table to augment my datetable I generated with the

period number added to the datetable.

Something like:

01/07/2018 Period 1

...

31/07/2018 Period 1

01/08/2018 Period 2

etc.

This using the period end date specified.

I have the date table etc and merging it with this new table I should be able to manage, just sure about the two questions regarding getting it in the right format.

v-lili6-msft · Accepted Answer

hi,@kvbe&nbsp; &nbsp; After my research, you can do these follow my steps as below:Basic dataStep1:Select column A and duplicate&nbsp;this columnand then select the duplicate column replace valueStep2:duplicate the tableStep3:Merge basic table with duplicate tableAnd then expand the tableStep4:Add a conditional columnStep5:filter custom column is 2Step6:Add the index column Step7:select Index column ,click Add Column-&gt;Standard-&gt;ModuloStep8filter column Modulo is 0Step9:select Column A and replace the valueStep10:Remove unnecessary column and rename the column&nbsp;Result:&nbsp;here is demo, please try it.https://www.dropbox.com/s/ablf38rvrhravgf/Power%20Query%20conversion%20from%20single%20columns%20to%20multiple%20columns%20based%20on%20rowname%20index.pbix?dl=0&nbsp;Best Regards,Lin&nbsp;&nbsp;&nbsp;&nbsp;