Extracting the Actual Values from Value.Type

Power Query
1

This should be a quick solve for you M fans out there. How do you extract the actual values from Value.Type()? When I apply this, I get the following, which is neither expandable nor extractable.

image

I ultimately was able to extract the correct values with this brute force mess, but there’s got to be an easier way, right?

image
image1049×656 27 KB

Thanks for your help! Sample PBIX file attached below.

2

Silly question… What are you hoping to achieve?

3

He is trying to get the data type of each row.

4

Alright then…

@BrianJ use Table.Schema

let
    Source = #"Extended Date Table Query"(#date(2020, 1, 1), #date(2021, 12, 31), 7, "Sunday", null, null),
    Schema = Table.Schema(Source)
in
    Schema
2 Likes
5

@Melissa,

Bingo! Exactly what I was looking for. I knew there had to be a function for this - I’ve gotten pretty good at the M function Google scavenger hunt, but never would have found this one (I was sure it would be somehow associated with Type. or .Type).

Thanks!

  • Brian
6

I know, it’s a great function…
Do a self merge to get those values back

let
    Source = #"Extended Date Table Query"(#date(2020, 1, 1), #date(2021, 12, 31), 7, "Sunday", null, null),
    FilterRows = Table.SelectRows(Source, each [Date] = #date(2021, 2, 9)),
    Unpivot = Table.UnpivotOtherColumns(FilterRows, {}, "Attribute", "Value"),
    Rename = Table.RenameColumns(Unpivot,{{"Attribute", "Field"}, {"Value", "Example Value"}}),
    GetSchema = Table.Schema( Source),
    AddIndex = Table.AddIndexColumn(Rename, "Index", 0, 1, Int64.Type),
    SelfMerge = Table.NestedJoin(AddIndex, {"Index"}, GetSchema, {"Position"}, "TypeName", JoinKind.LeftOuter),
    ExtractType = Table.RemoveColumns( Table.ExpandTableColumn(SelfMerge, "TypeName", {"TypeName"}, {"TypeName"}), {"Index"} )
in
    ExtractType
1 Like