Merge two Power Query function into one

[brawnystaff]

I have trying to consolidate two Power Query functions into one. Both query the same online resource using the same type of identifier, but, they are different in that some of the results return individual information and some results return business information. If I have a mix of both individual and business identifiers, I got error messages for the ones that are for the wrong type depending on which function I use.

The difference between the two is in the line #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers" in that the business function has a "org_name" column and the individual function has a first_name, mdl_name, last_name, and gndr_sw header. Is there some type of variable I can create to encompass all?? See below..

(MedStatusBus)=>
let
    Source = Json.Document(Web.Contents("https://data.cms.gov/resource/ykfi-ffzq.json?npi=" & Text.From(MedStatusBus))),
    Source1 = Source{0},
    #"Converted to Table" = Record.ToTable(Source1),
    #"Transposed Table" = Table.Transpose(#"Converted to Table"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"npi", Int64.Type}, {"pecos_asct_cntl_id", Int64.Type}, {"enrlmt_id", type text}, {"provider_type_cd", type text}, {"provider_type_desc", type text}, {"state_cd", type text}, {"org_name", type text}})
in
    #"Changed Type"

(MedStatusIndividual)=>
let
    Source = Json.Document(Web.Contents("https://data.cms.gov/resource/ykfi-ffzq.json?npi=" & Text.From(MedStatusIndividual))),
    Source1 = Source{0},
    #"Converted to Table" = Record.ToTable(Source1),
    #"Transposed Table" = Table.Transpose(#"Converted to Table"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"npi", Int64.Type}, {"pecos_asct_cntl_id", Int64.Type}, {"enrlmt_id", type text}, {"provider_type_cd", type text}, {"provider_type_desc", type text}, {"state_cd", type text}, {"first_name", type text}, {"mdl_name", type text}, {"last_name", type text}, {"gndr_sw", type text}})
in
    #"Changed Type"

 

[C Moore]

Changed Type I've found hurts more than it helps especially when generated by BI without input. Do you need them formatted coming out of the function? Or can you set them up to format later.

 

[brawnystaff]

Can set them up to format later..

 

[C Moore]

Delete the Change Type from each function (don't forget to update result in step) and see if it runs.

 

[brawnystaff]

Yes, it did run this time with no errors. The only issue is that when I expand the Table results, it does not give the business names, only the individual names (individual identifiers were listed first in the Table), but, the information provided is good enough. Not sure if there is way to fix this, but will mark as Solved. Thanks C Moore..