Provando a fare tutto con un'unica query, scopiazzando un po' dalla rete, ho tirato fuori questi passaggi, ma c'è qualcosa che non va nel passaggio "Pivoted Column"...
Codice: Seleziona tutto
let
Source = Excel.CurrentWorkbook(){[Name="Tabella1"]}[Content],
#"Filtrate righe" = Table.SelectRows(Source, each Text.Contains([CAMPO4], "|")),
#"Removed Columns" = Table.RemoveColumns(#"Filtrate righe",{"CAMPO4"}),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Removed Columns", {{"CAMPO5", Splitter.SplitTextByDelimiter("|", QuoteStyle.None), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "CAMPO5"),
CAMPO5 = Table.AddIndexColumn(#"Split Column by Delimiter", "Index", 1, 1),
#"Removed Columns1" = Table.RemoveColumns(#"Filtrate righe",{"CAMPO5"}),
#"Split Column by Delimiter2" = Table.ExpandListColumn(Table.TransformColumns(#"Removed Columns1", {{"CAMPO4", Splitter.SplitTextByDelimiter("|", QuoteStyle.None), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "CAMPO4"),
CAMPO4 = Table.AddIndexColumn(#"Split Column by Delimiter2", "Index", 1, 1),
#"Consolidated" = CAMPO5 & CAMPO4,
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Consolidated, {"Index"}, "Attribute", "Value"),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Unpivoted Other Columns", {{"Index", type text}}), List.Distinct(Table.TransformColumnTypes(#"Unpivoted Other Columns", {{"Index", type text}})[Index]), "Index", "Value"),
#"Transposed Table" = Table.Transpose(#"Pivoted Column"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true])
in
#"Promoted Headers"
...provando a fare la stessa cosa seguendo il tuo (
Andrea90 ) procedimento ci sono riuscito!
Codice: Seleziona tutto
let
Origine = Excel.CurrentWorkbook(){[Name="Tabella1"]}[Content],
Tab0 = Table.TransformColumnTypes(Origine,{{"CAMPO1", type text}, {"CAMPO2", type number}, {"CAMPO3", type number}, {"CAMPO4", type text}, {"CAMPO5", type any}}),
Indice0 = Table.TransformColumnTypes( Table.AddIndexColumn(Tab0, "Indice", 1, 1),{{"Indice", Int64.Type}}),
SplitCampo4 = Table.ExpandListColumn(Table.TransformColumns(Indice0, {{"CAMPO4", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "CAMPO4"),
TipoCampo4 = Table.TransformColumnTypes(SplitCampo4,{{"CAMPO4", type text}}),
Indice1 = Table.TransformColumnTypes( Table.AddIndexColumn(TipoCampo4, "Indice.1", 1, 1),{{"Indice.1", Int64.Type}}),
TabCampo4 = Table.RemoveColumns(Indice1,{"CAMPO5"}),
SplitCampo5 = Table.ExpandListColumn(Table.TransformColumns(Table.TransformColumnTypes(Indice0, {{"CAMPO5", type text}}, "it-IT"), {{"CAMPO5", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "CAMPO5"),
TipoCampo5 = Table.TransformColumnTypes(SplitCampo5,{{"CAMPO5", Int64.Type}}),
TabCampo5 = Table.TransformColumnTypes(Table.AddIndexColumn(TipoCampo5, "Indice.1", 1, 1),{{"Indice.1", Int64.Type}}),
MergeTabCampo4TabCampo5 = Table.NestedJoin(TabCampo4,{"Indice", "Indice.1"},TabCampo5,{"Indice", "Indice.1"},"TabCampo5",JoinKind.LeftOuter),
TabCampo4TabCampo5ConIndici = Table.ExpandTableColumn(MergeTabCampo4TabCampo5, "TabCampo5", {"CAMPO5"}, {"CAMPO5"}),
Risultato = Table.RemoveColumns(TabCampo4TabCampo5ConIndici,{"Indice", "Indice.1"})
in
Risultato
con un paio di esercizi, non si può certo imparare il codice M, ma ora che me lo avete fatto conoscere, vi toccherà sopportarmi
