Converting data in a report into data that be loaded into a database table

[DonnaRonna]

Hi,

First off, I love this board! Very helpful.

I have a report (excel format) of data that has the amounts for each date in a separate column:

Descriptor Descriptor Jan-18 Feb-18 Mar-18 and so forth

 

[DonnaRonna]

wow, I hit a key and submitted this without finishing. sorry about that.

Descriptor Descriptor Jan-18 Feb-18 Mar-18
Widget Red $5 $10 $10

What I need, in order to load the data into a table in the database is:

Descriptor Descriptor $ Date
Widget Red $5 Jan-18
Widget Red $10 Feb-18
Widget Red $12 Mar-18

Any ideas?

Thanks in advance

DonnaRonna

 

[sandy666]

If you are able to use PowerQuery aka Get&Transform, try

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Descriptor[/td][td=bgcolor:#5B9BD5]Descriptor2[/td][td=bgcolor:#5B9BD5]Jan-18[/td][td=bgcolor:#5B9BD5]Feb-18[/td][td=bgcolor:#5B9BD5]Mar-18[/td][td][/td][td=bgcolor:#70AD47]Descriptor[/td][td=bgcolor:#70AD47]Descriptor2[/td][td=bgcolor:#70AD47]Value[/td][td=bgcolor:#70AD47]Attribute[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Widget[/td][td=bgcolor:#DDEBF7]Red[/td][td=bgcolor:#DDEBF7]$5[/td][td=bgcolor:#DDEBF7]$10[/td][td=bgcolor:#DDEBF7]$10[/td][td][/td][td=bgcolor:#E2EFDA]Widget[/td][td=bgcolor:#E2EFDA]Red[/td][td=bgcolor:#E2EFDA]$5[/td][td=bgcolor:#E2EFDA]Jan-18[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]Widget[/td][td]Red[/td][td]$10[/td][td]Feb-18[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]Widget[/td][td=bgcolor:#E2EFDA]Red[/td][td=bgcolor:#E2EFDA]$10[/td][td=bgcolor:#E2EFDA]Mar-18[/td][/tr]
[/table]

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Descriptor", "Descriptor2"}, "Attribute", "Value"),
    #"Reordered Columns" = Table.ReorderColumns(#"Unpivoted Other Columns",{"Descriptor", "Descriptor2", "Value", "Attribute"})
in
    #"Reordered Columns"[/SIZE]