none
Is there a function in Power Query to take a group heading and have it repeat in a field? RRS feed

  • Question

  • Is there a feature or function in Power Query or M code to take row value and put it into a different field?

    Before:

    After:

    Thursday, September 27, 2018 1:24 AM

Answers

  • Assuming that the indentations are spaces, and that the number of spaces is consistent for each level, you can try something like the following:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        AddedIndentation = Table.AddColumn(Source, "Indentation", each Text.PositionOf([Column1], Text.Start(Text.Trim([Column1]),1))),
        DistinctIndentations = List.Sort(List.Distinct(AddedIndentation[Indentation])),
        AddedContinent = Table.AddColumn(AddedIndentation, "Continent", each if [Indentation] = DistinctIndentations{0} then [Column1] else null),
        FilledContinent = Table.FillDown(AddedContinent,{"Continent"}),
        AddedRegion = Table.AddColumn(FilledContinent, "Region", each if [Indentation] = DistinctIndentations{1} then Text.TrimStart([Column1], " ") else null),
        FilledRegion = Table.FillDown(AddedRegion,{"Region"}),
        AddedCountry = Table.AddColumn(FilledRegion, "Country", each if [Indentation] = DistinctIndentations{2} then Text.TrimStart([Column1], " ") else null),
        FilteredRows = Table.SelectRows(AddedCountry, each ([Region] <> null) and ([Country] <> null)),
        RemovedColumn = Table.RemoveColumns(FilteredRows,{"Column1", "Indentation"})
    in
        RemovedColumn

    If the indentations are tabs, the solution can still work if the number of tabs for each level are consistent. In the AddedIndentation and subsequent steps, you would trim tabs instead of spaces.

    Thursday, September 27, 2018 5:58 AM

All replies

  • Is the indenting consistent throughout the dataset? Is so, how many spaces are the region names indented, and how many spaces are the country names indented?
    Thursday, September 27, 2018 3:31 AM
  • Excel 365 Pro Plus with PowerPivot and Power Query (aka Get & Transform)
    With default indent.
    With PQ Pivot() and Fill()
    http://www.mediafire.com/file/1u0k7ieztj128gy/09_26_18a.xlsm/file
    http://www.mediafire.com/file/on8y6eo6qg6ec46/09_26_18a.pdf/file
    Thursday, September 27, 2018 4:28 AM
  • Herbert,

    Where did your Dent column come from? I don't see it in the original data provided.

    Thursday, September 27, 2018 4:39 AM
  • Assuming that the indentations are spaces, and that the number of spaces is consistent for each level, you can try something like the following:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        AddedIndentation = Table.AddColumn(Source, "Indentation", each Text.PositionOf([Column1], Text.Start(Text.Trim([Column1]),1))),
        DistinctIndentations = List.Sort(List.Distinct(AddedIndentation[Indentation])),
        AddedContinent = Table.AddColumn(AddedIndentation, "Continent", each if [Indentation] = DistinctIndentations{0} then [Column1] else null),
        FilledContinent = Table.FillDown(AddedContinent,{"Continent"}),
        AddedRegion = Table.AddColumn(FilledContinent, "Region", each if [Indentation] = DistinctIndentations{1} then Text.TrimStart([Column1], " ") else null),
        FilledRegion = Table.FillDown(AddedRegion,{"Region"}),
        AddedCountry = Table.AddColumn(FilledRegion, "Country", each if [Indentation] = DistinctIndentations{2} then Text.TrimStart([Column1], " ") else null),
        FilteredRows = Table.SelectRows(AddedCountry, each ([Region] <> null) and ([Country] <> null)),
        RemovedColumn = Table.RemoveColumns(FilteredRows,{"Column1", "Indentation"})
    in
        RemovedColumn

    If the indentations are tabs, the solution can still work if the number of tabs for each level are consistent. In the AddedIndentation and subsequent steps, you would trim tabs instead of spaces.

    Thursday, September 27, 2018 5:58 AM