locked
Pivot/unpivot to desire format RRS feed

  • Question

  • Hi,

     

    How do I pivot/unpivot below to the desire result in power query editor?

    From

    To

    Thursday, July 16, 2020 7:26 AM

Answers

  • in 3 steps, (it also be done directly with the GUI)

    let
    fn_text_to_table = (input as text) =>
    	Table.PromoteHeaders(
    		Table.FromRows(
    			Json.Document(
    				Binary.Decompress(
    					Binary.FromText( input, BinaryEncoding.Base64),
    					Compression.Deflate
    				)
    			)
    		)
    	),
    source = fn_text_to_table("i45W8kvMTVXSUXLOz8kvAtJuRaWZJUqxOtFKWfkZeUCByNScnPxyIMOxoCAnFVnGKacUpNO/KDEvHSIRkFqSCjLEvSg1FaQiIDWxCEUiKDUFLJ1YANQQCwA="),
    
    group = Table.Group(
    	source, 
    	{"Name"}, 
    	{
    		{"Color", each Text.Combine(List.Transform(_[Color], Text.From), "|")},
    		{"Fruit", each Text.Combine(List.Transform(_[Fruit], Text.From), "|")}
    	}
    ),
    split_color = Table.SplitColumn(
    	group, 
    	"Color", 
    	Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Color1", "Color2"}
    ),
    split_fruit = Table.SplitColumn(
    	split_color, 
    	"Fruit", 
    	Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Fruit1", "Fruit2"}
    )
    in split_fruit






    • Edited by anthony34 Friday, July 17, 2020 6:31 AM
    • Marked as answer by marc_hll Friday, July 17, 2020 4:56 PM
    Friday, July 17, 2020 6:25 AM

All replies

  • in 3 steps, (it also be done directly with the GUI)

    let
    fn_text_to_table = (input as text) =>
    	Table.PromoteHeaders(
    		Table.FromRows(
    			Json.Document(
    				Binary.Decompress(
    					Binary.FromText( input, BinaryEncoding.Base64),
    					Compression.Deflate
    				)
    			)
    		)
    	),
    source = fn_text_to_table("i45W8kvMTVXSUXLOz8kvAtJuRaWZJUqxOtFKWfkZeUCByNScnPxyIMOxoCAnFVnGKacUpNO/KDEvHSIRkFqSCjLEvSg1FaQiIDWxCEUiKDUFLJ1YANQQCwA="),
    
    group = Table.Group(
    	source, 
    	{"Name"}, 
    	{
    		{"Color", each Text.Combine(List.Transform(_[Color], Text.From), "|")},
    		{"Fruit", each Text.Combine(List.Transform(_[Fruit], Text.From), "|")}
    	}
    ),
    split_color = Table.SplitColumn(
    	group, 
    	"Color", 
    	Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Color1", "Color2"}
    ),
    split_fruit = Table.SplitColumn(
    	split_color, 
    	"Fruit", 
    	Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Fruit1", "Fruit2"}
    )
    in split_fruit






    • Edited by anthony34 Friday, July 17, 2020 6:31 AM
    • Marked as answer by marc_hll Friday, July 17, 2020 4:56 PM
    Friday, July 17, 2020 6:25 AM
  • Thanks @anthony34, works great!
    Friday, July 17, 2020 4:57 PM