Power Query / Power BI Transform Data problem: every new item under a category into its own column?

Adairene - this will get you most of what you want:

becomes this:

The issue is, for B/C, where there were no values to expand, it would need to figure out how many times to replicate that value.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIE4gilWB0ELxKFFwXmOaGodEbwYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Column1", "Column2"}, {{"All Rows", each _, type table [Column1=nullable text, Column2=nullable text, Column3=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Column3 Expansion", each [All Rows][Column3]),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Column3 Expansion", each Text.Combine(List.Transform(_, Text.From), ":"), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Column3 Expansion", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Column3 Expansion.1", "Column3 Expansion.2", "Column3 Expansion.3"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Split Column by Delimiter",{"All Rows", "Column3 Expansion.1", "Column3 Expansion.2", "Column3 Expansion.3"}),
    #"Expanded All Rows" = Table.ExpandTableColumn(#"Removed Other Columns", "All Rows", {"Column1", "Column2", "Column3"}, {"Column1", "Column2", "Column3"}),
    #"Transposed Table" = Table.Transpose(#"Expanded All Rows"),
    #"Transposed Table1" = Table.Transpose(#"Transposed Table")
in
    #"Transposed Table1"

There is no Fill Right feature. There is a fill down, but even transposing the table to do that would cause issues as the number of columns to fill down becomes dynamic as well. I would need to understand a bit more about the logic of what you are doing. You seem to be denormalizing the data,which is unusual for Power BI.

 

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.