Need Some Suggestions for Pivoting Data

[Brandi Bell]

Hey All,

I tried posting this question in another similar post, but I am not getting any response, so I am trying a new one, since this is slightly different.
I have data that a chemical company gathers from their field personnel that is formatted to be the easiest solution for them. They have a grid of Wells and Chemicals that they treat on various days of a "monthly" cycle. They enter the number of gallons of chemical that they treat a well with under the corresponding date. The issue is, I need this in a more data table format of Well, Chemical, Date and Gallons; therefore I need this transposed and partly duplicated (well name and chemical). This would be given to them as a template of sorts so they can return the data to me each month. I thought this would be an easy formula, but the way they have it set up has thrown me for a loop.
Any suggestion, ideas or comical commentary would be much appreciated.

Thanks,
Brandi

 

[AlanSidman]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Well Name", type text}, {"Product", type text}, {"Gals", Int64.Type}, {"Freq", Int64.Type}, {"Date", type text}, {"12/24", Int64.Type}, {"12/25", Int64.Type}, {"12/26", Int64.Type}, {"12/27", Int64.Type}, {"12/28", Int64.Type}, {"12/29", Int64.Type}, {"12/30", Int64.Type}, {"12/31", Int64.Type}, {"1/1", Int64.Type}, {"1/2", Int64.Type}, {"1/3", Int64.Type}, {"1/4", Int64.Type}, {"1/5", Int64.Type}}),
    #"Removed Top Rows" = Table.Skip(#"Changed Type",2),
    #"Removed Columns" = Table.RemoveColumns(#"Removed Top Rows",{"Gals", "Freq", "Date"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Well Name", "Product"}, "Attribute", "Value")
in
    #"Unpivoted Other Columns"

Use Power Query/Get and Transform. Delete the unnecessary columns and rows. Then Unpivot the remaining data holding columns A & B as constant.

 

[vletm]

Brandi Bell
You wrote that:
I tried posting this question in another similar post, but I am not getting any response,
Have You read:
https://chandoo.org/forum/threads/site-rules-new-users-please-read.294/
... then You'll know why no answers.

 

[Brandi Bell]

@AlanSidman Thank You! Completely forgot about Power Query! That worked.
@vletm I do apologize, I did not notice the posting a new thread, I didn't want to clog the feed up with the same question, but I do understand where that can be a problem. Thank You.