Calculate values from a form

Hi Anonymous ,

How about this:

let
    Source = Excel.Workbook(File.Contents("D:\Power BI\Temp Testing Case\Calculate values from a form.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Categoría", type text}, {"Dominio", type text}, {"Dimensión", type text}, {"Ítem", type any}}),
    #"Filled Down" = Table.FillDown(#"Changed Type",{"Categoría", "Dominio"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Filled Down",{{"Ítem", type text}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type1", {{"Ítem", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Ítem"),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Ítem", Int64.Type}})
in
    #"Changed Type2"

In addition, if it won't involve returning two "1" in "11", you can refer to this post: DAX function to search for multiple values in strings and count the number of times each value occur.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.