Move columns in power query

[dzaba]

Hi,

I stucked with one step in power query

I need to make a list of Users. My table look right now like this

USER_ID	work type	Value	work type2	Value2
woRda	Other	1		0
fErmo	Other	1		0
tuReD	service	1		0
RevaN	parts	1		0
Eellh	Other	0,5	parts	0,5
Opijs	Other	0,5	parts	0,5
Jerys	Other	1		0
hiieA	Other	1		0
HTWaa	parts	1		0
XZU3cc10	Other	0,5	Other	0,5
DGG55533	Other	1		0
XZU3230	Other	1		0
Xeeeec	Other	1		0
vvvRRRG	parts	1		0
SDF343	parts	1		0
XZU0851	Other	1		0
MERcheas	Other	0,5	sales	0,5
Ttyywy	sales	1		0
OPDOS	sales	1		0
Jdksiu	sales	1		0
Loeurk	sales	1

to make next analysis I need to have the table

User | work | value. So I need to transfer 

work type2

Value2

 under the column

work type

Value

Any idea? 

[edhans · Accepted solution]

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.

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] <> " " 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"

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

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 Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How 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.