Forum Discussion
Move columns in power query
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?
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.
1 Reply
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.