Forum Discussion

PQQ__'s avatar
PQQ__
New Member
10 months ago
Solved

Power Query - taking subheadings out of column and adding to rows

I have a Power Query which uses a spreadsheet that looks something like the first image below. Currently I have to add a new column and manually pull out the subheadings (between the ****************...
Need Help
  • PhilipTreacy's avatar
    PhilipTreacy
    10 months ago

    Hi PQQ__ 

     

    Download example PBIX file with the code below

     

    This approach just uses some Custom Columns

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nVFNC4JAEP0ri6cKLxX9APMjIcvFVqHEg9iiku2AroT/PvVgm2yIDXOYeW9g3psJQ0VR+4zUUFkJIcDYQrbmOJfpSaH0AJ5o3XY+ezB4sbZyeuKY8ySjrJJQkp2IAI+LbnixQQ4kMc+BVUvJwh+SLNc1kO76Hpkl3waODEi/VRo9ta/LlJYdo7cxwHpGaUXHqJVXWdtgF58GmzgueYMsgPuYkeoWTrD76wRY88gVBebZN+f98AYg+RIu4iYtoWadfEIOHwKgmPhq7zygrKaCq+3YVfQG", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [LOCATION = _t, KEY = _t, STATUS = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"LOCATION", type text}, {"KEY", type text}, {"STATUS", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if Text.Contains([LOCATION], "**") then #"Added Index"[LOCATION]{[Index] - 1} else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
    #"Added Custom1" = Table.AddColumn(#"Filled Down", "TYPE", each if [STATUS] <> "" then [Custom] else ""),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Index", "Custom"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"TYPE", "LOCATION", "KEY", "STATUS"})
in
    #"Reordered Columns"

     

     

    I'm curious what the end goal is with all of this though, the data you're working with isn't in a great layout for reporting/analysis.

     

    Regards

     

    Phil

     

     

m_dekorte's avatar
m_dekorte
Icon for Resident Rockstar rankResident Rockstar
10 months ago

Hi PQQ__ 

 

Create a new blank query, give that query this name: addTypeCol
Open the advanced editor and replace everything you see there with this code

(tbl as table, colName as text, optional newColName as text) as table =>
let
    newCol = newColName ?? "TYPE",
    lst = try Table.Column(tbl, colName) otherwise error " No column with the name " & """" & colName & """" & " was found.",
    l = List.Buffer(List.Positions(lst)),
    c = List.Accumulate(
        l,
        [v = null, r = {}],
        (s, a) => let val = lst{a} in [
            v = if Text.Length(val ?? "") >1 and (try Text.StartsWith(lst{a+1} ?? "", "*****") otherwise false) then val else s[v],
            r = s[r] & {
                if List.AnyTrue( {Value.Equals(val, null), Text.StartsWith(val ?? "", "*****"), val = v, 
                    Value.Equals(val, ""), Text.Contains(val ?? "", " total", Comparer.OrdinalIgnoreCase)}) 
                then null
                else s[v]
            }
        ]
    )[r],
    ans = Table.RenameColumns( 
        Table.FromColumns(
            Table.ToColumns(tbl) & {c},
            Value.Type(tbl & #table(type table[XXX=text], {}))
        ), {"XXX", newCol}
    )
in 
    ans

Now you can invoke this function by selecting a table and providing the name for the column that contains the subheadings in your example LOCATION then you hit invoke. That should do it. Optionally you can give the new column that is added a different name, by default it will be named TYPE.

A new query is created, that will look something like this.

Hope this is helpful