Forum Discussion

Birdjo's avatar
Birdjo
Icon for Resolver II rankResolver II
8 years ago
Solved

Set column type while expanding from merged query

Hello, Here's a quick question: I am expanding a column after merging. In the source the column type is text and has values like: 0333, 0555, 0888 While expanding it expands like a whole number an...
Need Help
  • Birdjo's avatar
    Birdjo
    8 years ago

    cwebb 

    I am working with Excel using Excel.Workbook(Web.Contents())

    In matter of fact the type doesn't matter untill I don't lose the zeros. Like 0003 > 3, 0005 > 5.

     

    MarcelBeug the change of the data type occurs while expanding and zeros are lost. I guess there's nothing to get them back.
    It is very nice how you values remain their zeros while their type is a number.

    My temporary solution was to change the type to a number everywhere and duplicate the column with type text in the table that I visualize. Works pretty good but it would be better if that was not necessary. 


    Thank you both!

  • Birdjo's avatar
    Birdjo
    Icon for Resolver II rankResolver II
    8 years ago

    Hello cwebb,

    Would you, please, be more specific?
    I have already tried setting the type in the function like this

    = Table.ExpandTableColumn(#"Merged Queries", "Shifts", {"Personnel Number"}, {"Personnel number"}, type text)

    But it won't works because Table.ExpandTableColumn doesn't accept a fifth parameter.

    Table.NestedJoin function also doesn't allow a parameter for setting types.

     

     

    • cwebb's avatar
      cwebb
      Icon for Advocate V rankAdvocate V
      8 years ago

      Can you give me more details about the data sources you're working with, and the query you're trying to write? I've just done some testing with the latest version of Power BI Desktop and it seems like Table.NestedJoin() automatically respects the data types of the columns from the tables it's joining, so there's no need to do anything special in code. I don't know whether this is new functionality though. 

       

      Chris

      • Birdjo's avatar
        Birdjo
        Icon for Resolver II rankResolver II
        8 years ago

        cwebb 

        I am working with Excel using Excel.Workbook(Web.Contents())

        In matter of fact the type doesn't matter untill I don't lose the zeros. Like 0003 > 3, 0005 > 5.

         

        MarcelBeug the change of the data type occurs while expanding and zeros are lost. I guess there's nothing to get them back.
        It is very nice how you values remain their zeros while their type is a number.

        My temporary solution was to change the type to a number everywhere and duplicate the column with type text in the table that I visualize. Works pretty good but it would be better if that was not necessary. 


        Thank you both!

  • MarcelBeug's avatar
    MarcelBeug
    Icon for Community Champion rankCommunity Champion
    8 years ago

    Still it is starnge that those text values are transfomed to numbers.

     

    Even if the column would be typed as number, then still the merge doesn't effect the actual values (which are texts with leading zeroes).

     

    So there must be something else, before the merge, that cause the values to be transformed to numbers.

     

    Coincidentally I published a video this morning with an example of grouping tables, illustrating that the default code would change data types to any, and how that can be correccted before the tables are expanded.

    The fragment starts at 4:08; keep watching until at least 5:50.

     

     

    Back to your case, here is some example data:

     

    Query Shifts:

    #table(type table[ID =Int64.Type, Personnel Number = Int64.Type],{{1, "0333"},{2, "0555"},{3, "0888"}})

     

    Query Table1:

    = #table({"Key","Person"},{{11, 1},{12, 2},{13, 3}})

     

    Query Merge1:

    let
    Source = Table.NestedJoin(Table1,{"Person"},Shifts,{"ID"},"Shifts",JoinKind.LeftOuter),
    #"Expanded Shifts" = Table.ExpandTableColumn(Source, "Shifts", {"Personnel Number"}, {"Personnel Number"})
in
    #"Expanded Shifts"

     

    Result from query Merge1: