Extracting the Actual Values from Value.Type

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?

Thanks for your help! Sample PBIX file attached below.

Silly question… What are you hoping to achieve?

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

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

@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

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