Forum Discussion

elliotdixon's avatar
elliotdixon
Icon for Responsive Resident rankResponsive Resident
10 years ago
Solved

Power Query - import txt file with multiple lines per row - change to one row

Hi All, I am trying to import a txt file into Power Query however the length of the txt data is increasing and has spilled over to take up more than one line. I need to get multiple lines on the txt...
Need Help
  • ImkeF's avatar
    ImkeF
    10 years ago

    So we need to apply some more M(agic) here:

     

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
    IndexFirst = Table.AddColumn(#"Added Index", "Custom", each if [Column1]="<Start>" then [Index] else null),
    #"Filled Down" = Table.FillDown(IndexFirst,{"Custom"}),
    GroupAndCombine = Table.Group(#"Filled Down", {"Custom"}, {{"Count", each Text.Combine(_[Column1]), type table}}),
    SplitAgain = Table.AddColumn(GroupAndCombine, "Custom.1", each Text.Split([Count], "><")),
    Show = Table.ExpandListColumn(SplitAgain, "Custom.1"),
    SplitColumns = Table.AddColumn(Show, "NewCols", each List.FirstN(Text.SplitAny([Custom.1], "<,>"),2)),
    Magic = Table.Group(SplitColumns, {"Custom"}, {{"Magic", each Table.PromoteHeaders(Table.FromColumns(_[NewCols])), type table}}),
    MagicMagic = Table.Combine(Magic[Magic])
in
    MagicMagic

    edited step "SplitColumns"

ImkeF's avatar
ImkeF
Icon for Community Champion rankCommunity Champion
10 years ago

Try this code:

 

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if [Column1]="<Start>" then [Index] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
    #"Grouped Rows" = Table.Group(#"Filled Down", {"Custom"}, {{"Count", each Text.Combine(_[Column1]), type table}})
in
    #"Grouped Rows"
elliotdixon's avatar
elliotdixon
Icon for Responsive Resident rankResponsive Resident
10 years ago

Hi ImkeF Thanks! Thats great. It does exactly what I need.

 

Only thing is that I can't now seem to split the resulting table that is created in PowerQuery.

 

 

How do i expand in Power Query to start modelling the data into the correct columns?

Cheers.

  • ImkeF's avatar
    ImkeF
    Icon for Community Champion rankCommunity Champion
    10 years ago

    So we need to apply some more M(agic) here:

     

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
    IndexFirst = Table.AddColumn(#"Added Index", "Custom", each if [Column1]="<Start>" then [Index] else null),
    #"Filled Down" = Table.FillDown(IndexFirst,{"Custom"}),
    GroupAndCombine = Table.Group(#"Filled Down", {"Custom"}, {{"Count", each Text.Combine(_[Column1]), type table}}),
    SplitAgain = Table.AddColumn(GroupAndCombine, "Custom.1", each Text.Split([Count], "><")),
    Show = Table.ExpandListColumn(SplitAgain, "Custom.1"),
    SplitColumns = Table.AddColumn(Show, "NewCols", each List.FirstN(Text.SplitAny([Custom.1], "<,>"),2)),
    Magic = Table.Group(SplitColumns, {"Custom"}, {{"Magic", each Table.PromoteHeaders(Table.FromColumns(_[NewCols])), type table}}),
    MagicMagic = Table.Combine(Magic[Magic])
in
    MagicMagic

    edited step "SplitColumns"

    • elliotdixon's avatar
      elliotdixon
      Icon for Responsive Resident rankResponsive Resident
      10 years ago

      Wow ImkeF Thanks heaps. You completly solved my whole process!
      I really need to learn more about M language! So powerful when used by the right hands.

       

      Amazing help. Cheers.

      ED.