Forum Discussion

vinit_dawane's avatar
vinit_dawane
New Member
2 years ago

Power Query Operation to append 3 categorical columns and their respective measures

I am trying to append 3 date columns from different months into single column (this stage has been reached post multiple operations) into a single one in Power Query editior. Not sure how to do this

 

From this :

 

To this :

 

 

Please suggest

 

Need Help
Tips and Tricks

3 Replies

  • dufoq3's avatar
    dufoq3
    Icon for Community Champion rankCommunity Champion
    2 years ago

    Hi vinit_dawane, Create blank query, replace whole code whith this one.

     

    Here you have similar versions (v1 and v2) how to achieve it:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZRbqgMhDIb3Ms+Fmqijs5YyOyh9O/s/Lebya4TSwoefuZjm9TooPRM9OXE+Hke+vl9EQAr/COOZctyP4XHwjJQinpNsXjZWxXOSxMtwt3oFchieky4eEvXqXA0ByVpfhQzUO+3UKZ6TJp6Tal4LfWmhvgY3qdfhruH1kGffxLuCd80dZiT2Dt/P1D1CQsMD4h6t8YCoR5C5egx3DY/nF2Ukp3l57SeQLB7OlHpl7gIBsXhlE2+ZDkKi9VXISb0wL0BkPmkzL9RCP1vIs228Ht6vzxkwEvd8Fki8K9SHRDxOaz85rfUBsX6ybxP53zoxjyAn9Xx3yDs40f8f40ypt2wTQiLz4gTyLPMpQiJ9AWJzzTXEqyEeEvXO+ZUJiO5dILZ3uc2vTEB0v8AZ72cP9fWQZ9/EC/PixN7hil5Oa1+c6D4DYvOZ5/3y+Xu//efnEES/738=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date1 = _t, Count1 = _t, Date2 = _t, Count2 = _t, Date3 = _t, Count3 = _t]),
    v1_ToCols = Table.ToColumns(Source),
    v1_Odd = List.Combine(List.Alternate(v1_ToCols,1,1,1)),
    v1_Even = List.Combine(List.Alternate(v1_ToCols,1,1,0)),
    v1_ToTable = Table.FromColumns({v1_Odd, v1_Even}, {"Date", "Count"}),
    v1_ChangedType = Table.TransformColumnTypes(v1_ToTable,{{"Date", type date}, {"Count", type number}}, "en-US"),
    v2_DateCols = Table.SelectColumns(Source, List.Select(Table.ColumnNames(Source), each Text.StartsWith(_,"Date"))),
    v2_CountCols = Table.SelectColumns(Source, List.Select(Table.ColumnNames(Source), each Text.StartsWith(_,"Count"))),
    v2_ToTable = Table.FromColumns({List.Combine(Table.ToColumns(v2_DateCols)), List.Combine(Table.ToColumns(v2_CountCols))}, {"Date", "Count"}),
    v2_ChangedType = Table.TransformColumnTypes(v2_ToTable,{{"Date", type date}, {"Count", type number}}, "en-US")
in
    v2_ChangedType

     

     

     

  • AlienSx's avatar
    AlienSx
    Icon for Super User rankSuper User
    2 years ago

    vinit_dawane 

    let
    Source = your_table,
    tx = Table.ToList(
        Source, (x) => List.Zip({{x{0}, x{2}, x{4}}, {x{1}, x{3}, x{5}}})
    ),
    tbl = #table({"Date", "Count"}, List.Combine(tx))
in
    tbl
  • ThxAlot's avatar
    ThxAlot
    Icon for Super User rankSuper User
    2 years ago
    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZRbqgMhDIb3Ms+Fmqijs5YyOyh9O/s/Lebya4TSwoefuZjm9TooPRM9OXE+Hke+vl9EQAr/COOZctyP4XHwjJQinpNsXjZWxXOSxMtwt3oFchieky4eEvXqXA0ByVpfhQzUO+3UKZ6TJp6Tal4LfWmhvgY3qdfhruH1kGffxLuCd80dZiT2Dt/P1D1CQsMD4h6t8YCoR5C5egx3DY/nF2Ukp3l57SeQLB7OlHpl7gIBsXhlE2+ZDkKi9VXISb0wL0BkPmkzL9RCP1vIs228Ht6vzxkwEvd8Fki8K9SHRDxOaz85rfUBsX6ybxP53zoxjyAn9Xx3yDs40f8f40ypt2wTQiLz4gTyLPMpQiJ9AWJzzTXEqyEeEvXO+ZUJiO5dILZ3uc2vTEB0v8AZ72cP9fWQZ9/EC/PixN7hil5Oa1+c6D4DYvOZ5/3y+Xu//efnEES/738=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date1 = _t, Count1 = _t, Date2 = _t, Count2 = _t, Date3 = _t, Count3 = _t]),

    Transformed = Table.FromColumns(List.Transform(List.Zip(List.Split(Table.ToColumns(Source), 2)), each List.Combine(_)), {"Date","Count"})
in
    Transformed