• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Help me with the VBA Code

With Power Query, you can unpivot the data. Here is the Mcode and the file attached.

Code: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Removed Columns" = Table.RemoveColumns(Source,{"Stock"}),
    #"Replaced Value" = Table.ReplaceValue(#"Removed Columns",null,0,Replacer.ReplaceValue,{"Process", "Name", "9:00", "Name  ", "10:00", "Name2", "11:00", "Name3", "12:00", "Name4", "1:00", "Name5", "3:00", "Name6", "4:00", "Name7", "5:00", "Name8", "6:00", "Name9", "6:30", "Name10", "7:00", "Name11", "7:30", "Name12", "8:00", "Name13", "8:30"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Replaced Value", {"Process"}, "Attribute", "Value"),
    #"Removed Columns1" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
    #"Added Index" = Table.AddIndexColumn(#"Removed Columns1", "Index", 0, 1),
    #"Inserted Modulo" = Table.AddColumn(#"Added Index", "Modulo", each Number.Mod([Index], 2), type number),
    #"Added Custom" = Table.AddColumn(#"Inserted Modulo", "Custom", each if [Modulo] = 1 then [Value] else null),
    #"Filled Up" = Table.FillUp(#"Added Custom",{"Custom"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Up", each ([Modulo] = 0)),
    #"Removed Columns2" = Table.RemoveColumns(#"Filtered Rows",{"Index", "Modulo"}),
    #"Added Custom1" = Table.AddColumn(#"Removed Columns2", "Custom.1", each 86.61),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom1",{{"Custom.1", "Gauge"}, {"Value", "Name"}, {"Custom", "Value"}})
in
    #"Renamed Columns"

Review PQ
In the attached file
Click on any cell in the new table
On the Data Tab, click on Queries & Connections
In the right window, double click to open Query
Review PQ steps
M-code basics:
- "let" is the start of a query
- "in" is the closing of a query
- each transformation step sits in between those 2 lines
- each step line is ended by a comma, except the last one
- "Source" is always the first step (Source Data)
- After "in" you have the last step referenced
 

Attachments

  • Book3.xlsx
    37.6 KB · Views: 5
Can you please guide me how i can implement this to check is this is working or not?
Actually i am new to power query
Thanks for your time
 
Start by looking at the link in my signature. You will need to be running 2010 or a later version. Please advise your version before we go further.
 
Glad to hear. If you want to expand your PQ skills, pick up a copy of "M is for (Data) Monkey" It is available through Amazon.
 
Add a 2 lines of code
Code: 
= Table.AddColumn(#"Renamed Columns", "Date2", each DateTime.LocalNow())
= Table.TransformColumnTypes(#"Added Custom2",{{"Date2", type date}})

This will add the date when you refresh/run the Power Query

If you want the time included, then only add the first line of code.
 
Here is amended Mcode.

Code: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Gauge", each if [Column1] = "Guage" then [Column2] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Gauge"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Filled Down",{"Gauge", "Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27", "Column28", "Column29", "Column30"}),
    #"Removed Top Rows" = Table.Skip(#"Reordered Columns",1),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    #"Removed Columns" = Table.RemoveColumns(#"Promoted Headers",{"Stock"}),
    #"Replaced Value" = Table.ReplaceValue(#"Removed Columns",null,0,Replacer.ReplaceValue,{"Process", "Name", "9:00", "Name  ", "10:00", "Name2", "11:00", "Name3", "12:00", "Name4", "1:00", "Name5", "3:00", "Name6", "4:00", "Name7", "5:00", "Name8", "6:00", "Name9", "6:30", "Name10", "7:00", "Name11", "7:30", "Name12", "8:00", "Name13", "8:30"}),
    #"Renamed Columns" = Table.RenameColumns(#"Replaced Value",{{"86.61", "Gauge"}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Renamed Columns", {"Gauge","Process"}, "Attribute", "Value"),
    #"Removed Columns1" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
    #"Added Index" = Table.AddIndexColumn(#"Removed Columns1", "Index", 0, 1),
    #"Inserted Modulo" = Table.AddColumn(#"Added Index", "Modulo", each Number.Mod([Index], 2), type number),
    #"Added Custom1" = Table.AddColumn(#"Inserted Modulo", "Custom", each if [Modulo] = 1 then [Value] else null),
    #"Filled Up" = Table.FillUp(#"Added Custom1",{"Custom"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Up", each ([Modulo] = 0)),
    #"Removed Columns2" = Table.RemoveColumns(#"Filtered Rows",{"Index", "Modulo"}),
    #"Added Custom3" = Table.AddColumn(#"Removed Columns2", "Date2", each DateTime.LocalNow()),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom3",{{"Date2", type date}}),
    #"Reordered Columns1" = Table.ReorderColumns(#"Changed Type",{"Process", "Value", "Custom", "Gauge", "Date2"})
in
    #"Reordered Columns1"

New file attached. Also note that in line two of the code Gauge is spelled Guage as that is how you spelled in the workbook. Be aware that this needs to be corrected if you change the spelling in the workbook. The table in this code has been expanded to include Row 1 which holds the Gauge.
 

Attachments

  • Book1.xlsx
    28 KB · Views: 1
Last edited:
sir i change 86.61 to something else but it shows error i need so when changes 86,61 to some other vale it changes automatically in the table we transformed
 
This is a bit complicated as what you are asking is not a simple task. I will be travelling for then next week and will not have Internet available to me. If you can wait, I will get back to this, or hopefully someone else will step in. In the meantime, if you need to make adjustments, you will need to do it manually in the code.
 
You are a lucky guy. I had a few minutes before departing on my trip and a brainstorm. Here is your new code. In your original data, change the gauge number. Then go to the output sheet and double click on the Refresh Icon on the Data Tab.

Code: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Demoted Headers" = Table.DemoteHeaders(Source),
    #"Transposed Table" = Table.Transpose(#"Demoted Headers"),
    #"Added Custom" = Table.AddColumn(#"Transposed Table", "Custom", each if Text.StartsWith([Column1],"Column") then null else [Column1]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Column1"}),
    #"Transposed Table1" = Table.Transpose(#"Removed Columns"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Process", type text}, {"Stock", type number}, {"Name", type text}, {"9:00", Int64.Type}, {"Name  ", type text}, {"10:00", Int64.Type}, {"Name2", type text}, {"11:00", Int64.Type}, {"Name3", type text}, {"12:00", type any}, {"Name4", type text}, {"1:00", Int64.Type}, {"Name5", type text}, {"3:00", Int64.Type}, {"Name6", type text}, {"4:00", Int64.Type}, {"Name7", type text}, {"5:00", Int64.Type}, {"Name8", type text}, {"6:00", Int64.Type}, {"Name9", type text}, {"6:30", Int64.Type}, {"Name10", type text}, {"7:00", Int64.Type}, {"Name11", type text}, {"7:30", Int64.Type}, {"Name12", type text}, {"8:00", Int64.Type}, {"Name13", type text}, {"8:30", Int64.Type}, {"Date2", type any}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Guage", each if [Process] = "Guage" then [Stock] else null),
    #"Filled Up" = Table.FillUp(#"Added Conditional Column",{"Guage"}),
    #"Removed Bottom Rows" = Table.RemoveLastN(#"Filled Up",1),
    #"Removed Columns1" = Table.RemoveColumns(#"Removed Bottom Rows",{"Stock"}),
    #"Replaced Value" = Table.ReplaceValue(#"Removed Columns1",null,0,Replacer.ReplaceValue,{"Process", "Name", "9:00", "Name  ", "10:00", "Name2", "11:00", "Name3", "12:00", "Name4", "1:00", "Name5", "3:00", "Name6", "4:00", "Name7", "5:00", "Name8", "6:00", "Name9", "6:30", "Name10", "7:00", "Name11", "7:30", "Name12", "8:00", "Name13", "8:30", "Date2", "Guage"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Replaced Value",{"Guage", "Process", "Name", "9:00", "Name  ", "10:00", "Name2", "11:00", "Name3", "12:00", "Name4", "1:00", "Name5", "3:00", "Name6", "4:00", "Name7", "5:00", "Name8", "6:00", "Name9", "6:30", "Name10", "7:00", "Name11", "7:30", "Name12", "8:00", "Name13", "8:30", "Date2"}),
    #"Removed Columns2" = Table.RemoveColumns(#"Reordered Columns",{"Date2"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns2", {"Guage", "Process"}, "Attribute", "Value"),
    #"Added Index" = Table.AddIndexColumn(#"Unpivoted Other Columns", "Index", 0, 1),
    #"Inserted Modulo" = Table.AddColumn(#"Added Index", "Modulo", each Number.Mod([Index], 2), type number),
    #"Added Custom1" = Table.AddColumn(#"Inserted Modulo", "Custom", each if[Modulo]=1 then [Value] else null),
    #"Filled Up1" = Table.FillUp(#"Added Custom1",{"Custom"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Up1", each ([Modulo] = 0)),
    #"Removed Columns3" = Table.RemoveColumns(#"Filtered Rows",{"Attribute", "Index", "Modulo"}),
    #"Reordered Columns1" = Table.ReorderColumns(#"Removed Columns3",{"Process", "Value", "Custom", "Guage"}),
    #"Added Custom2" = Table.AddColumn(#"Reordered Columns1", "Date", each DateTime.LocalNow())
in
    #"Added Custom2"
 

Attachments

  • Book1.xlsx
    28.6 KB · Views: 3
You must log in or register to reply here.
Back
Top