Power Query - разобрать очень странный файл

Question

У меня есть файл, который выглядит так:

AA,DATA,DATA,DATA
BB,DATA,DATA,DATA,DATA
ZZ,DATA,DATA,DATA,DATA,DATA,DATA
ZZ,DATA,DATA,DATA,DATA,DATA
CC,DATA,DATA
ZZ,DATA,DATA,DATA
ZZ,DATA,DATA,DATA
ZZ,DATA,DATA,DATA,DATA

Как видите, это очень недружелюбно. Стартовые строки ZZ означают, что они связаны с предыдущей записью; поэтому в моем примере BB разделен на 3 строки, а CC разделен на четыре строки. Чего я хотел бы достичь в Power Query (Excel 2013), так это получить таблицу, которая будет содержать только записи AA, BB и CC. ZZ будет объединен с их соответствующими записями BB и CC. Количество записей ZZ может отличаться.
Я новичок в Power Query, и я предполагаю, что я начал со слишком тяжелой проблемы, чтобы иметь дело;) Любая конкретная идея о том, как я должен составить таблицу и / или список, чтобы помочь мне, была бы признательна.

Спасибо.

С уважением, Мартин

Answer 1

Я знаю, что это старый вопрос, и, возможно, мое решение тогда не сработало. Но этот PQFL хорошо работает, когда я пробую его в PowerBI Desktop.

let
  RecordKey = (t,n) => Table.Range(t,n,1){0}[Key],

  NeedsMerged = (t,n) =>
    try
      if RecordKey(t,n+1) = "ZZ" then true else false
    otherwise false,

  MergeWithNext = (t,n) =>
    if NeedsMerged(t,n) then
      let
        thisRow = Table.Range(t, n, 1),
        nextRow = Table.Range(t, n+1, 1)
      in  
        {
        true,  
        Table.Range(t, 0, n) &
        Table.TransformColumns( thisRow, {"Data", (_) => _ & nextRow{0}[Data] } ) &
        Table.Range(t, n + 2)
        }
    else {false,t},

  DoNeededMerging = (t,n) =>
    if Table.RowCount(t) < n then t
    else
      let 
        res = MergeWithNext(t,n),
        resp = res{0},
        tbl = res{1}
      in
        if resp then
          @DoNeededMerging(tbl,n)
        else
          @DoNeededMerging(tbl,n+1),

  Filename = "C:\the_data_file.csv",
  DataTable = Table.FromList(Lines.FromText(Text.FromBinary(File.Contents(Filename))), Splitter.SplitTextByDelimiter(","), {"Key","Data"}, null, ExtraValues.List),
  DataTable2 = DoNeededMerging( DataTable , 0)

in
  DataTable2

Answer 2

Я вполне уверен, что вы не можете сделать это в PowerQuery. Вы можете использовать VBA, чтобы получить правильные данные, а затем поместить их в PowerQuery. Вот код, который может это сделать.

Sub NormalizeData()

    Dim sFile As String, lFile As Long
    Dim vaLines As Variant
    Dim vaData As Variant
    Dim i As Long, j As Long
    Dim dc As Scripting.Dictionary
    Dim lStart As Long

    'Open file and read in all the data
    sFile = "K:\testfile.txt"
    lFile = FreeFile

    Open sFile For Input As lFile
    vaLines = Split(Input$(LOF(lFile), lFile), vbNewLine)
    Close lFile

    For i = LBound(vaLines) To UBound(vaLines)

        vaData = Split(vaLines(i), ",")

        If vaData(0) <> "ZZ" Then
            'Output existing dictionary if it exists
            If Not dc Is Nothing Then Debug.Print Join(dc.Items, ",")

            'Create a new dictionary and start at the first column (0)
            Set dc = New Scripting.Dictionary
            lStart = 0
        Else
            'starts with ZZ so skip the first column
            lStart = 1
        End If

        'Add the data to the dictionary
        For j = lStart To UBound(vaData)
            dc.Add dc.Count + 1, vaData(j)
        Next j

    Next i

    'Output the last dictionary
    If Not dc Is Nothing Then Debug.Print Join(dc.Items, ",")

End Sub