how to change table into rows with specific headers?

[walmaghribi]

hi

i have a table that looks like this

Jan        Feb      Mar      Apr     May     Jun    July     Jan18      Feb18     Mar18      Apr18     May18     Jun18    July18

$25     $569    $25      $698   $589    $569  $678   $569       $678       $698          $589       $25       $569       $25 

i wanted to be converted into

Month  Estimate   Negotiated

Jan        25               569

Feb       569             678

Mar      25               698

Apr      698              589

May     589              25

.......etc 

were Estimated is reading from Jan, feb, mar....and negotiated is reading from Jan18, feb18...etc

Jun

July

[Sean · Accepted solution]

Please note I've used the sample data exactly as it was posted above!

STEPs

1) Home tab - Use Headers as First Row

2) Transform tab - Transpose

3) Add Column tab - select Column1 - Extract - First Characters - 3 - OK (Rename column to Month)

4) Add Column tab - Index Column - From 0

5) Transform tab - Index Column still selected - Standard - Modulo - 7 and then Standard - Add - 1 (Rename column Sort)

6) Add Column tab - Conditional Column - If Column1 contains 18 - 2018 else 2017 - OK

7) Remove Column1

😎 Transform tab - select the Custom Column - Pivot Column - Values column - Column2 - and - DON'T AGGREGATE!

9) select the Sort column and sort ascending - move it to the end if you wish

10) rename the 2017 and 2018 columns Estimate and Negotiated respectively

HTH!  

[Ashish_Mathur · Accepted solution]

Hi,

This M code works fine for me.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjJV0lEyNbMEkmCmmaUFSMDCEi5sZm6BxkZWg6o/NhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Jan = _t, Feb = _t, Mar = _t, Apr = _t, May = _t, Jun = _t, Jul = _t, Jan18 = _t, Feb18 = _t, Mar18 = _t, Apr18 = _t, May18 = _t, Jun18 = _t, Jul18 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Jan", Int64.Type}, {"Feb", Int64.Type}, {"Mar", Int64.Type}, {"Apr", Int64.Type}, {"May", Int64.Type}, {"Jun", Int64.Type}, {"Jul", Int64.Type}, {"Jan18", Int64.Type}, {"Feb18", Int64.Type}, {"Mar18", Int64.Type}, {"Apr18", Int64.Type}, {"May18", Int64.Type}, {"Jun18", Int64.Type}, {"Jul18", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value"),
    #"Split Column by Position" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByPositions({0, 3}, false), {"Attribute.1", "Attribute.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Attribute.1", type text}, {"Attribute.2", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each if [Attribute.2]=null then "Estimated" else "Negotiated"),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index", "Attribute.2"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Custom]), "Custom", "Value"),
    #"Added Custom1" = Table.AddColumn(#"Pivoted Column", "Custom", each "1/"&[Attribute.1]&"/2017"),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Added Custom1", {{"Custom", type date}}, "en-IN"),
    #"Sorted Rows" = Table.Sort(#"Changed Type with Locale",{{"Custom", Order.Ascending}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Sorted Rows",{"Custom"})
in
    #"Removed Columns1"

Hope this helps.

[walmaghribi]

thank you so much , this is excellent, one more thing, i use this to display a graph, and it should show any previouse months (of the current month) for estimated as 0 and only show negotiated.

so i used if function but PBI sayid i cannot use text with number conditioning, could you help me with that?

[Sean]

@Ashish_MathurYou have to adjust your code

The sample data says July not Jul and July18 not Jul18

That's why I started my response above with => Please Note I've used the data exactly as posted in the original question.

[kgova]

try unpivot column 

[walmaghribi]

i tried that, it didnt work in a usable way

[Sean · Accepted solution]

Please note I've used the sample data exactly as it was posted above!

STEPs

1) Home tab - Use Headers as First Row

2) Transform tab - Transpose

3) Add Column tab - select Column1 - Extract - First Characters - 3 - OK (Rename column to Month)

4) Add Column tab - Index Column - From 0

5) Transform tab - Index Column still selected - Standard - Modulo - 7 and then Standard - Add - 1 (Rename column Sort)

6) Add Column tab - Conditional Column - If Column1 contains 18 - 2018 else 2017 - OK

7) Remove Column1

😎 Transform tab - select the Custom Column - Pivot Column - Values column - Column2 - and - DON'T AGGREGATE!

9) select the Sort column and sort ascending - move it to the end if you wish

10) rename the 2017 and 2018 columns Estimate and Negotiated respectively

HTH!