Move columns in power query

Question

Hi,&nbsp;I stucked with one step in power query&nbsp;I need to make a list of Users. My table look right now like this&nbsp;USER_IDwork typeValuework type2Value2woRdaOther1&nbsp;0fErmoOther1&nbsp;0tuReDservice1&nbsp;0RevaNparts1&nbsp;0EellhOther0,5parts0,5OpijsOther0,5parts0,5JerysOther1&nbsp;0hiieAOther1&nbsp;0HTWaaparts1&nbsp;0XZU3cc10Other0,5Other0,5DGG55533Other1&nbsp;0XZU3230Other1&nbsp;0XeeeecOther1&nbsp;0vvvRRRGparts1&nbsp;0SDF343parts1&nbsp;0XZU0851Other1&nbsp;0MERcheasOther0,5sales0,5Ttyywysales1&nbsp;0OPDOSsales1&nbsp;0Jdksiusales1&nbsp;0Loeurksales1&nbsp;&nbsp;&nbsp;to make next analysis I need to have the table&nbsp;User | work | value. So I need to transfer&nbsp;work type2Value2&nbsp;under the columnwork typeValue&nbsp;Any idea?&nbsp;

edhans · Accepted Answer

See this code. It turns this:

Into this:

You weren't clear on what you wanted, so if Work Type 2 had a value and Work Type 1 said Other, I used Work Type 2.
With the values, I just added them together.
&nbsp;
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZBBa4QwEIX/iuTsITYN9FrQtUi3luiyZcVDiFPMrsUl0Yj/vvGWYqcNDOHxPV5epmnIMopOkpiUUw/G34mfyA8lbdyQz8x8jSidZgGpVxaM0wp2XICTb17dpZnsjmYwDH2QTWMeeDe1ucq7vtp/XQWY1aI9e63hGaUv9VlKtOXH5cSUSuiuQqg2Y5rnnHPG0He2pAdGcQ7+KBQ754QQOVq0Sg/skf31D/rEEzT+mAnVg9yv2soBwlXX07ouawB+5pTvaVmhtOhuVs8ofh1hNrdfcETa9hs=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [USER_ID = _t, #"work type" = _t, Value = _t, #"work type2" = _t, Value2 = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,",",".",Replacer.ReplaceText,{"Value", "Value2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"Value", Currency.Type}, {"Value2", Currency.Type}}),
    #"Replaced Value1" = Table.ReplaceValue(#"Changed Type",null,0,Replacer.ReplaceValue,{"Value", "Value2"}),
    #"Added New Work Type" = Table.AddColumn(#"Replaced Value1", "New Work Type", each if [work type] = "Other" and [work type2] &lt;&gt; " " then [work type2] else [work type]),
    #"Inserted Addition" = Table.AddColumn(#"Added New Work Type", "New Value", each [Value] + [Value2], Currency.Type),
    #"Changed Type1" = Table.TransformColumnTypes(#"Inserted Addition",{{"New Work Type", type text}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type1",{"USER_ID", "New Work Type", "New Value"})
in
    #"Removed Other Columns"
&nbsp;
How to use M code provided in a blank query:1) In Power Query, select New Source, then Blank Query2) On the Home ribbon, select "Advanced Editor" button3) Remove everything you see, then paste the M code I've given you in that box.4) Press Done5) See this article if you need help using this M code in your model.
&nbsp;
If that isn't what you want, provide sample output with an explanation of how you got there.
How to get good help fast. Help us help you.How To Ask A Technical Question If you Really Want An AnswerHow to Get Your Question Answered Quickly - Give us a good and concise explanationHow to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

Forum Discussion

Related content

Power Query or Calculated columns

Find Most Recent Date Relative to another Date Column in Power Query

Power Query Pivoting columns issue

Power query limit columns?

Conditional Column in power query