Desired Outcome via Query Pt 2

Power Query
1

First thing is I’d like to say thanks for the help on Pt 1. As mentioned, my manager was coming back with extra requests and the post evolved a bit.

They would like to be able to see additional fields in columns (region, milepost, Assessment length etc.) such as how Dig Id is is now in a Column and was solved in Pt 1.

If there is a pattern to follow similar to how "Keep Dig Id’ was added I’m all ears and will add in what I need! However after trying about 30 different ways (without success) to add additional code/field names into the ‘Keep Dig Id’ line, I’m back on a new thread.

Apologies for the confusion on my earlier post and the disregard of forum rules.

Capture
Capture1825×710 73.9 KB

Query Trial2.pbix (82.7 KB)
Query ExampleSunday.xlsx (18.6 KB)

2

Hi @cms418,

See if this meets your requirement. You can change the selection in the FilterRows step.

let
    Source = Excel.Workbook(File.Contents("\\edmusers\Schwebic\Desktop\Query ExampleSunday.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    Transpose = Table.Transpose(Sheet1_Sheet),
    MergeCols = Table.CombineColumns(Transpose,{"Column1", "Column2"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"Merged"),
    FilterRows = Table.SelectRows(MergeCols, each ([Merged] <> "|Index")),
    TransposeBack = Table.Transpose(FilterRows),
    PromoteHeaders = Table.PromoteHeaders(TransposeBack, [PromoteAllScalars=true]),
    UnpivotOthers = Table.UnpivotOtherColumns(PromoteHeaders, List.Select( Table.ColumnNames(PromoteHeaders), each Text.StartsWith( _, "|" )), "Attribute", "Value"),
    SplitAttribute = Table.SplitColumn(UnpivotOthers, "Attribute", Splitter.SplitTextByDelimiter( "|", QuoteStyle.Csv), {"WBS", "Cost Item"}),
    RenameCols = Table.RenameColumns( SplitAttribute, List.Zip( { Table.ColumnNames( SplitAttribute), List.Transform( Table.ColumnNames(SplitAttribute), each Text.TrimStart( _, "|")) } )),
    ChType = Table.TransformColumnTypes(RenameCols,{{"Value", Currency.Type}})
in
    ChType

I hope this is helpful

1 Like
3

Outstanding thanks so much @Melissa. Can you point me to a resource to learn this ? Would it be your Power Query Master class?

1 Like
4

Hi @cms418,

Although I would recommend that course :wink: … not specifically. That’s a PQ Fundamentals course, it covers: set up of the UI, key concepts in M, best practices and so on stuff you’ll need to learn to move beyond just using the UI alone.

If you want to get more out of PQ, this is what eDNA has on offer (at this time) in the order I would recommend.

.
Although these next 2 courses don’t focus on PQ alone, they do offer content you can work through

.
This is the content which is also available on eDNA YouTube channel

.
And for advancing your skills

1 Like